Results 1 to 8 of 8
  1. #1
    Join Date
    Mar 2002
    Location
    Shropshire, UK.
    Posts
    25

    Unhappy Unanswered: Access query, comparing times and dates??

    <rant>
    OH MY GOD I SOOOOOOOOOOOO HATE SQL!!!!!!!!!!!!!!!!!!!
    </rant>

    I have a table, OnlineUsers, set up like this:

    ID: AutoNumber
    UserID: Text
    URL: Text
    LoggedIn: Date/Time (General)
    LastKnownRequest: Date/Time (General)
    InSiteMsgStatus: Number

    It is populated, with one row, thus:
    ID=130
    UserID=Webmaster
    URL=/
    LoggedIn=05/09/02 20:41:31
    LastKnownRequest=09/05/02 20:57:26
    InSiteMsgStatus=0

    So, I execute a query, like this:

    SELECT *
    FROM OnlineUsers
    WHERE LastKnownRequest<#9/5/2002 20:24:26#;

    WHY OH WHY OH WHY does it return the row with a time of 20:57?
    I could cry my $$$$ eyes out!! I would tear my hair out, but I don't have any left!

    HELP - PLEASE HELP ME!!!!!

    Mark.
    Mark Wills.

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    it is probably because 05/09 is not the same as 09/05

    try first selecting the dates from the table using FORMAT() to find out whether they were stored sept 5 or may 9

    select ID
    , format(LoggedIn,'yyyy/mm/dd')
    , format(LastKnownRequest,'yyyy/mm/dd')
    from OnlineUsers

    then when you are constructing your WHERE conditions, try always specifying the date strings in iso format, so there's no ambiguity

    where LastKnownRequest < #2002-09-05 20:24:26#

    rudy
    http://rudy.ca/

  3. #3
    Join Date
    Sep 2002
    Location
    Coventry, Warwickshire, UK
    Posts
    74
    Dates in an SQL statement MUST always be in the format #mm/dd/yyyy#.

    When you view dates in a tables thet are presented to you in the format you have defines Regional and Language Options in Control Panel. With Access they are always stored in mm/dd/yyyy format, so that is how you must specify them in SQL.
    Roger Hampson
    XI - ecs (UK) Ltd

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    i'm sorry roger, that's just not true

    i have access 97 open here in front of me and i just ran

    insert into testdate (thedate)
    values (#9/5/02#)

    insert into testdate (thedate)
    values (#2002-09-06#)

    insert into testdate (thedate)
    values (#sept 11 2002#)

    and they all went into the table just fine

    then i ran

    select thedate from testdate
    where thedate >= #9/5/02#

    select thedate from testdate
    where thedate >= #2002-09-06#

    select thedate from testdate
    where thedate >= #sept 11 2002#

    and these all ran just fine and produced exactly the expected results

    rudy

  5. #5
    Join Date
    Sep 2002
    Location
    Coventry, Warwickshire, UK
    Posts
    74
    I'm quite sure that they all went into the database without any problems, but the question is "Is #9/5/02# the 5th Sept 2002 or 9th May 2002?", Access cannot make that decision because it is ambiguous therefore it assumes it is 5th Sept 2002. If you meant 9th May 2002, you would have to input it as #5/9/2002#. As I said, Access assumes mm/dd/yyyy.

    Your second example is also ambiguous, but your third example is not.
    Roger Hampson
    XI - ecs (UK) Ltd

  6. #6
    Join Date
    Mar 2002
    Location
    Shropshire, UK.
    Posts
    25
    I concur with Roger. I have seen those exact results:

    1/7/2002 - access sees this as 1st July 2002
    7/1/2002 - access sees this as 7th Jan 2002
    8/17/2002 - access sees this as 17th August 2002
    17/8/2002 - access sees this as 17th August 2002

    In the end, I tried entering the dates in ISO (yyyy/mm/dd hh:mm:ss) and it seemed to like that!

    The problem arose with an ASP script that I had written. I wrote the script around the 20th of the month:

    20/8/2002 - access sees this as 20th August 2002
    8/20/2002 - access sees this as 20th August 2002

    However, come the 1st of the month:

    1/9/2002 - access sees this as 9th Jan 2002
    I wanted 1st September! *BANG*!
    Mark Wills.

  7. #7
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    "Is #9/5/02# the 5th Sept 2002 or 9th May 2002? Access cannot make that decision because it is ambiguous"

    i know what you mean, but access will interpret it one way and one way only, whenever the numbers can be reversed and still make sense

    in the case of 8/17 and 17/8, access will interpret both of them the same way (because one of the numbers is >12)

    you can change the way access interprets the "ambiguous" dates, simply by changing a setting -- the exact one escapes me at the moment, quite possibly it is in Control Panel

    i suggest you consult the access help file to be sure

    in any case, yyyy-mm-dd will never be ambiguous

    try submitting 2002-17-08 and you will see it rejected

    rudy

  8. #8
    Join Date
    Jul 2002
    Location
    Australia
    Posts
    147
    OT, but when working with the web, you should ALWAYS explicitly convert your dates IN and OUT of the database.

    Use a custom function such as this (ASP) for this task:
    http://www.adopenstatic.com/resource...formatdate.asp
    http://www.adopenstatic.com/faq/DateswithAccess.asp

    With Access, the best way to insert your dates is to input in ISO format or d/mmm/yyyy format as there can be no mixups in the month.
    http://www.flws.com.au/showusyourcod...te.asp?catID=3

    Cheers,
    Andrew
    There have been many posts made throughout the world.
    This was one of them.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •