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?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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)
Cdate will return a date in the format of the shortdate in your regional settings.  Be sure
#09/23/2004# is that same format.
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

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>


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!

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
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)
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.