Solved

SQL statement for selecting a date from a timestamp field

Posted on 2004-10-27
1,254 Views
Last Modified: 2007-12-19
Hello,

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!

Greetings,

SteffenM
0
Question by:SteffenM
    8 Comments
     
    LVL 5

    Expert Comment

    by:niblick
    Change:
    WHERE CDate(Date_Time) =

    To:
    Where Date_Time =
    0
     
    LVL 5

    Expert Comment

    by:jpontani
    Above would work, unless it has the times in it also.

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

    Expert Comment

    by:GRayL
    Cdate will return a date in the format of the shortdate in your regional settings.  Be sure
    #09/23/2004# is that same format.
    0
     
    LVL 44

    Expert Comment

    by:GRayL
    Disregard the above.

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

    0
     
    LVL 44

    Expert Comment

    by:GRayL
    Just did some checking and my ans above is just another way of saying what jpontani answered previously.
    0
     
    LVL 5

    Expert Comment

    by:perezjos
    hello,
    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>

    regards

    jose
    0
     
    LVL 58

    Accepted Solution

    by:
    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

    GRayL:
    * 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!
    0
     
    LVL 58

    Expert Comment

    by:harfang
    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)
    0

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    How to run any project with ease

    Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
    - Combine task lists, docs, spreadsheets, and chat in one
    - View and edit from mobile/offline
    - Cut down on emails

    It took me quite some time to sort out all the different properties of combo and list boxes available from Visual Basic at run-time. Not that the documentation is lacking: the help pages are quite thorough and well written. The problem was rather wh…
    Overview: This article:       (a) explains one principle method to cross-reference invoice items in Quickbooks®       (b) explores the reasons one might need to cross-reference invoice items       (c) provides a sample process for creating a M…
    Familiarize people with the process of utilizing SQL Server views from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Access…
    Familiarize people with the process of utilizing SQL Server stored procedures from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Micr…

    934 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

    Need Help in Real-Time?

    Connect with top rated Experts

    16 Experts available now in Live!

    Get 1:1 Help Now