[Last Call] Learn about multicloud storage options and how to improve your company's cloud strategy. Register Now


SQL statement for selecting a date from a timestamp field

Posted on 2004-10-27
Medium Priority
Last Modified: 2007-12-19

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!


Question by:SteffenM
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions

Expert Comment

ID: 12426204
WHERE CDate(Date_Time) =

Where Date_Time =

Expert Comment

ID: 12426249
Above would work, unless it has the times in it also.

Change CDate(Date_Time) to FormatDateTime(Date_Time,2)
LVL 44

Expert Comment

ID: 12427262
Cdate will return a date in the format of the shortdate in your regional settings.  Be sure
#09/23/2004# is that same format.
Prepare for your VMware VCP6-DCV exam.

Josh Coen and Jason Langer have prepared the latest edition of VCP study guide. Both authors have been working in the IT field for more than a decade, and both hold VMware certifications. This 163-page guide covers all 10 of the exam blueprint sections.

LVL 44

Expert Comment

ID: 12427310
Disregard the above.

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

LVL 44

Expert Comment

ID: 12427333
Just did some checking and my ans above is just another way of saying what jpontani answered previously.

Expert Comment

ID: 12427692
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>


LVL 58

Accepted Solution

harfang earned 800 total points
ID: 12430234
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!
LVL 58

Expert Comment

ID: 12430242
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)

Featured Post

Get your Conversational Ransomware Defense e‑book

This e-book gives you an insight into the ransomware threat and reviews the fundamentals of top-notch ransomware preparedness and recovery. To help you protect yourself and your organization. The initial infection may be inevitable, so the best protection is to be fully prepared.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Access custom database properties are useful for storing miscellaneous bits of information in a format that persists through database closing and reopening.  This article shows how to create and use them.
The Windows Phone Theme Colours is a tight, powerful, and well balanced palette. This tiny Access application makes it a snap to select and pick a value. And it doubles as an intro to implementing WithEvents, one of Access' hidden gems.
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.
This lesson discusses how to use a Mainform + Subforms in Microsoft Access to find and enter data for payments on orders. The sample data comes from a custom shop that builds and sells movable storage structures that are delivered to your property. …

650 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question