Problem with query based on date/time field in Access

[ralphk]

A query filtering on a date/time field does not return any values, but if I make it a text field it works just fine. The problem with leaving it a text field is that it does not sort according to the date. I'm using Access. [sig][/sig]

 

[Guest_imported]

To use a date variable in a query, the date should be created with CreateODBCDate():


<cfset thisdate= #CreateODBCDate(&quot;2000/09/30&quot#>

<cfquery datasource=&quot;#DSN#&quot; name=&quot;DateSearch&quot;>
Select * from TableName
where date_in = #thisdate#
</cfquery>

This asumes that the date in the table is iether native (exists by default using the $date function in access), OR was formatted to be an ODBC date before it was inserted.

 

[edwebdeveloper]

The other thing that you need to be careful with when you are comparing date/time objects in a database query is that when you use date_in = #thisdate# you will only get back records that match both the date and time. For instance:

You have 2000/09/30 00:00:00 in your database

and the #thisdate# your are passing actually reads:

2000/09/30 01:00:30

this will not pull up the 9/30 date in your database because the time object is different. It would be safer to make sure that your time portion of the field is the same as your queried value. (ie make them all set to whatever date plus&quot;00:00:00&quot; The other option is to use some sort of greater than or equal to/less than or equal to combination.

Just some thoughts on what I have discovered when using dates in my SQL queries. [sig][/sig]