Date Format MS ACCESS database

[tapuwa2002]

I have made a select statement with a date condition seems like its not reading the date condition please check the code below

Select *
From tbltest
WHERE (((Validity.ToDate)<><cfqueryPARAM value = "#31/8/2011#" CFSQLType = 'CF_SQL_DATE'>)

Please help I don't want dates that are equal to 31/08/2011 its getting the data from a access

[LyndonOHRC]

(((Validity.ToDate)<><cfqueryPARAM value = "#31/8/2011#" CFSQLType = 'CF_SQL_DATE'>)

Remove the #'s

(((Validity.ToDate)<><cfqueryPARAM value = "31/8/2011" CFSQLType = 'CF_SQL_DATE'>)

[arrgh]

31/8/2011

Also you can't use dd/mm/yyyy format. The standard functions expect values to be in U.S. date format ie mm/dd/yyyy. If your CF server is running under a locale where the standard is dd/mm/yyyy (like in England) then use the LS functions to parse the value first.
http://livedocs.adobe.com/coldfusion/8/htmldocs/help.html?content=Tags_p-q_18.html

<cfqueryparam value="#LSParseDateTime("31/8/2011")#" ....>

If your locale IS English/US, then you'll have to split it into month, day and year manually. Then use createDate() to construct the date.

<cfqueryparam value="#createDate(year, month, day)#" ....>

[LyndonOHRC]

Good catch, I didn't even notice the date format...

[arrgh]

I'm just glad it was the 31st. Often examples are ambiguous like 06/10 .. so you don't even know there's a problem until the query blows up. lol