Formatting Date Time Values for Access SQL in Delphi

Ever get the awful "Parameter object is improperly defined. Inconsistent or incomplete information was provided" JET error? Here's how to rectify the situation.

When you need to create a SQL query against ​an Access database where a date (or a date time) value is used you need to make sure the correct formatting is used.

For example, in a SQL query: "SELECT * FROM TBL WHERE DateField = '10/12/2008'" you want to get all the records from the table named TBL where a general date field DateField equals 10/12/2008.

Is the line above clear? Is that December, 10 or October, 12? Luckily, we are pretty sure the year in the query is 2008.

Should the date part of the query be specified as MM/DD/YYYY or DD/MM/YYYY or maybe YYYYMMDD? And do regional settings play a role here?

MS Access, Jet, Date Time Formatting

When using Access and JET (dbGo - ADO Delphi controls) the formatting of the SQL for the date field should *always* be:

Anything else might work in limited testing but can often lead to unexpected results or errors on the user's machine.

Here's a custom Delphi function you can use to format a date value for the Access SQL query.

For "January 29, 1973" the function will return the string '#1973-01-29#'.

Access SQL Date Time Format?

As for the date and time formatting, the general format is:

This is: #year-month-daySPACEhour:minute:second#

As soon as you construct a valid date time string for the SQL using the above general format and try it using any of Delphi's dataset components as TADOQuery, you will receive the awful "Parameter object is improperly defined. Inconsistent or incomplete information was provided" error at run-time!

The problem with the format above is in the ":" character - as it is used for parameters in parametrized Delphi queries. As in "... WHERE DateField = :dateValue" - here "dateValue" is a parameter and the ":" is used to mark it.

One way to "fix" the error is to use another format for date/time (replace ":" with "."):

And here's a custom Delphi function to return a string from a date time value you can use when constructing SQL queries for Access where you need to search for a date-time value:

The format looks weird but will result in the correctly formatted date time string value to be used in SQL queries!

Here's a shorter version using the FormatDateTime routine: