Time Values for Access SQL in Delphi

Format 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? Heres 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: #YYYY-MM-DD# Anything else might work in limited testing but can often lead to unexpected results or errors on the users machine. Heres 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 Delphis 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 heres 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: 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! Heres a shorter version using the FormatDateTime routine: function DateTimeForSQL(const dateTime : TDateTime) : string;begin   Ã‚  result : FormatDateTime(#yyyy-mm-dd hh.nn.ss#, dateTime) ; end;