Title: Date Time SQL Queries: Formatting Date Time Values for Access SQL in Delphi
Ever got the awful "Parameter object is improperly defined. Inconsistent or incomplete information was provided" JET error?
When you need to create a SQL query againts 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? 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:
#YYYY-MM-DD#
Anything else might work in limited testing, but can often lead to unexpected results or errors on users machine.
Here's a custom Delphi function you can use to format a date value for the Access SQL query.
function DateForSQL(const date : TDate) : string;
var
y, m, d : word;
begin
DecodeDate(date, y, m, d) ;
result := Format('#%.*d-%.*d-%.*d#',[4, y, 2, m, 2, d]) ;
end;
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:
#yyyy-mm-dd HH:MM:SS#
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 ".") :
#yyyy-mm-dd HH.MM.SS#
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 on a date time value:
function DateTimeForSQL(const dateTime : TDateTime) : string;
var
y, m, d : word;
hour, min, sec, msec : word;
begin
DecodeDate(dateTime, y, m, d) ;
DecodeTime(dateTime, hour, min, sec, msec) ;
result := Format('#%.*d-%.*d-%.*d %.*d.%.*d.%.*d#',[4, y, 2, m, 2, d, 2, hour, 2, min, 2, sec]) ;
end;
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:
function DateTimeForSQL(const dateTime : TDateTime) : string;
begin
result := FormatDateTime('#yyyy-mm-dd hh.nn.ss#', dateTime) ;
end;