locked
SQL DATETIME ERROR RRS feed

  • Question

  • HI

    I have some questions about Access Database(Sorry i cant find Access forum...)

    My Sql statement is:

    UPDATE BorrowHistory
    SET Whenreturned = #20/05/2009#
    WHERE WhenBorrowed=#26/05/2009 2:20 p.m.#;

    Everytime i run this query, it gives me a datetime error.
    When i change the date from #26/05/2009 2:20 p.m.# to #26/05/2009#, it works, does this mean Access can not deal with time?

    thanks in advance
    Tuesday, May 26, 2009 2:48 AM

Answers

  • In control pannel , region and language

    set it to English(US) or English(Australia), and it ends with PM

    before it was English(NewZealand), it is like '4:44:33 p.m'.
    Thursday, May 28, 2009 4:49 AM
  • Use "pm", not "p.m.".  Like this:

     
    SELECT #26/05/2009 2:20 pm#
    
    


    RBarryYoung 
    Tuesday, May 26, 2009 3:45 AM
  • Well it does not happen on my system like that, so it probably does have something to do with ither you system or your Access/Office language settings.

    It is often true that the output formats of Dates/Times is not always a valid input format.  I would recommend adjusting your Access/Office language settings to see if you can get them to match your System settings.  Otherwise, use explicit output formats to control your output or use code to edit your date inputs.
    RBarryYoung
    Tuesday, May 26, 2009 4:09 AM

All replies

  • Use "pm", not "p.m.".  Like this:

     
    SELECT #26/05/2009 2:20 pm#
    
    


    RBarryYoung 
    Tuesday, May 26, 2009 3:45 AM
  • thanks

    but the "26/05/2009 2:20 p.m." is generated by System.datetime.now, i didnt hard coded it

    and all records in my access ends with "p.m.",   i tried to change it to "pm" in access, but when i open the table again, they just returned to "p.m."

    any possible it is  because that im using the chinese version access?

    Tuesday, May 26, 2009 3:54 AM
  • Well it does not happen on my system like that, so it probably does have something to do with ither you system or your Access/Office language settings.

    It is often true that the output formats of Dates/Times is not always a valid input format.  I would recommend adjusting your Access/Office language settings to see if you can get them to match your System settings.  Otherwise, use explicit output formats to control your output or use code to edit your date inputs.
    RBarryYoung
    Tuesday, May 26, 2009 4:09 AM
  • Solved, thanks
    Thursday, May 28, 2009 2:39 AM
  • So how did you solve it?
    RBarryYoung
    Thursday, May 28, 2009 4:21 AM
  • In control pannel , region and language

    set it to English(US) or English(Australia), and it ends with PM

    before it was English(NewZealand), it is like '4:44:33 p.m'.
    Thursday, May 28, 2009 4:49 AM
  • Cool.  Thanks for the update.
    RBarryYoung
    Thursday, May 28, 2009 1:46 PM