SQL statement for selecting a date from a timestamp field


i am using an access database and i'm trying to get table entries matching a specific date in the field Date_time, which is a timestamp. My SQL string is:

SELECT CodecardID, User, Date_time from StdEval WHERE CDate(Date_Time) = #09/23/2004# ORDER BY user

even though there are entries for 09/23/2004, none are returned... what am I doing wrong? my guess is that the date format or the cast is wrong... Please help me!


Who is Participating?
harfangConnect With a Mentor Commented:
Sorry, the above comments contains some problems...

jpontani: the Format...() function returns a string, which you compare to a date. Not nice for the JetEngine

* CDate returns a date (independant of regional settings). When display that date, it will be converted (e.g. Debug.Print), or if you compare it to a string.
* DateValue also returns a date, but without the time info. Incidentally, both expect a string.
If "Date_Time" is a date, it will be converted to string for CDate, that will interpret the string and build a date, tranformed to a string for DateValue, interpreted there and finally returned...
(Sorry if I sound rude... The DateValue() suggestion is probably the best bet!)

Now, to the point.

If "Date_Time" is a date/time field (check the table structure), you do not need CDate().

    SELECT * FROM StdEval WHERE [Date_Time] = #09/23/2004#

BUT, the name of the field suggest that it contains a time... :) so, using GRayL's suggestion:

    SELECT * FROM StdEval WHERE DateValue([Date_Time]) = #09/23/2004#

Or, to let the JetEngine work alone without having to talk with VB all the time...

    SELECT * FROM StdEval WHERE [Date_Time] Between #09/23/2004# And #09/24/2004#

OK, OK, you do not want that midnight record... replace the second date with #09/23/2004 23:59:59#

If Date_Time is a string, justifying the "CDate()" function, we can start talking about regional settings...
In your query, create a new field with "Test: CDate([Date_Time])" and compare it to the column Date_Time.
Then report back here :)

Cheers all!
WHERE CDate(Date_Time) =

Where Date_Time =
Above would work, unless it has the times in it also.

Change CDate(Date_Time) to FormatDateTime(Date_Time,2)
Never miss a deadline with monday.com

The revolutionary project management tool is here!   Plan visually with a single glance and make sure your projects get done.

Cdate will return a date in the format of the shortdate in your regional settings.  Be sure
#09/23/2004# is that same format.
Disregard the above.

SELECT CodecardID,User,Date_time from StdEval WHERE DateValue(CDate(Date_Time)) = #09/23/2004# ORDER BY user;

Just did some checking and my ans above is just another way of saying what jpontani answered previously.
if you want a date to work all the time and you field is a datetime field use the odbc syntax:

for a date:
<b>SELECT CodecardID, User, Date_time from StdEval WHERE CDate(Date_Time) = {d '2004-23-09'} ORDER BY user</b>

for a time
<b>SELECT CodecardID, User, Date_time from StdEval WHERE CDate(Date_Time) = {t '00:23:00'} ORDER BY user </b>

for a timestamp
<b>SELECT CodecardID, User, Date_time from StdEval WHERE CDate(Date_Time) = {ts '2004-23-09 00:00:00'} ORDER BY user<b>


Sligh correction, I just checked and it turns out that DateValue does not expect a string. If a date is passed, no interpretation takes place, so my ironic comments backfired... :(
(sorry again GRayL)
All Courses

From novice to tech pro — start learning today.