Post reply

"Datetime" and "ISDate"

  • November 10, 2004 at 2:22 am

    #361608

    1. From BOL: "Microsoft® SQL Server™ rejects all values it cannot recognize as dates between 1753 and 9999."

    I assume, then, that dates outside that range must be stored as a char() type. Correct?

    2. Despite the statement in 1,  the SQL IsDate function returns TRUE in the following example

      DECLARE @datestring varchar(8)

      SET @datestring = '01/23/1234'            

      SELECT ISDATE(@datestring)

      

    BUT if one tries storing that "date" in a SQL datetime field (via INSERT, UPDATE) an error is generated. Why the discrepancy? Any good ways to know, other than by trying, if SQL will accept a given date?

     

    TIA,

    Bill

     

  • November 10, 2004 at 2:30 am

    #529571

    Yes, 1) is correct. That is how datetime datatype is implemented in SQL Server.

    2) Well, this is expected. Your datestring for the test is '01/23/12', which by all means conforms to 1)

    /Kenneth

  • November 10, 2004 at 2:54 am

    #529576

    Keneth,

     

    Thanks for the reply but I thought the date of '01/23/12' will INSERT into a table as January 1, 2012. That is definitely not my date: "01/23/1234".

    TIA,

     

    Bill 

  • November 10, 2004 at 3:04 am

    #529580

    Well, it does insert just well when I tried it.

    Maybe I should point out that you don't have the string "01/23/1234" anywhere in your example, since your variable for storing that is only 8 chars long - thus the actual string we're dealing with here is "01/23/12".  

    Your original format - "01/23/1234" - is not a valid SQL Server date

    I'd also like to promote the ISO format when dealing with dates (yyyymmdd). It's unambigous, means the same all over the world, and is not language dependant. Don't use '/' delimited date strings - it's a sure source for confusion.

    /Kenneth

  • November 11, 2004 at 6:29 am

    #529768

    Hehe didn't see that one comming 🙂

    DECLARE @datestring varchar(8)

  • November 11, 2004 at 10:42 am

    #529806

    Spent the morning working with a developer using C#, which decides to use 00010101 instead of NULL values. Great. Ended up using the DBNull class to get around this.

    Quand on parle du loup, on en voit la queue

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply