Dates and SQL 1

[dpaulson]

I'm having trouble using dates with an access database. The field in the database is type date/time. These dates are stored as dd/MM/yyyy format. When I use a date in my SQL which has a date for example "...<=#12/02/2006#... " (Feb. 12/2006) it returns records which include records whose dates are less or equal to 02/12/2006 or Dec. 2/2006. Any date I use which has a day > 12 whether it is in dd/mm/yyyy format or mm/dd/yyyy format return the correct records. What do I have to do to fix this.

David Paulson

 

[RoyVidar]

It doesn't matter how they are stored (well, they are stored, in Access I think as 8 byte floats, where the integer part represents days since 31/12/1899, and the decimal part seconds since midnight), if you use a dynamic sql string, you will need to provide it in an unambiguous format - see the db engine just sees the string, and only knows US format or ISO 8601.

[tt]...where mydatefield = #" & format$(dtDate, "yyyy-mm-dd") & "# ...[/tt]

Roy-Vidar

 

[dpaulson]

Thanks Roy. Works great. Now I can continue

David Paulson