Cast as datetime question

  • john schroeder-334489

    Valued Member

    Points: 74

    Comments posted to this topic are about the item Cast as datetime question

  • Bhavesh_Patel

    SSCrazy

    Points: 2259

    That was tricky one.

    I didn't knew that when a number is casted to DATETIME, that number is added '1/1/1900' and returns the resulting date.



    [font="System"]Bhavesh Patel[/font]

    http://bhaveshgpatel.wordpress.com/
  • Ben Leighton

    SSCommitted

    Points: 1838

    Nice question... demonstrates how the subtraction operation implicitly casts the result as an integer data type thus avoiding the error message that '435365' on its own would have caused.

  • ronmoses@gmail.com

    SSCarpal Tunnel

    Points: 4480

    A neat question, but since I didn't see a reason for any of the implicit conversions not to work, I assumed the one datetime value on the list had to be the correct answer. If there was another datetime option I probably would have had to guess.

    -----
    a haiku...

    NULL is not zero
    NULL is not an empty string
    NULL is the unknown

  • chrisn-585491

    SSCoach

    Points: 15896

    This doesn't work in SQL Server 2005 TSQL , end up with an a Msg 1305...

    :unsure:

  • ronmoses@gmail.com

    SSCarpal Tunnel

    Points: 4480

    chrisn-585491 (11/18/2009)


    This doesn't work in SQL Server 2005 TSQL , end up with an a Msg 1305...

    Works perfectly for me in 2005.

    -----
    a haiku...

    NULL is not zero
    NULL is not an empty string
    NULL is the unknown

  • Ben Leighton

    SSCommitted

    Points: 1838

    chrisn-585491 (11/18/2009)


    This doesn't work in SQL Server 2005 TSQL , end up with an a Msg 1305...

    :unsure:

    worked for me in 2005

  • chrisn-585491

    SSCoach

    Points: 15896

    :blush:

    Restarted Management Studio and the results work now as expected...

  • Dan Guzman - Not the MVP

    Hall of Fame

    Points: 3755

    Interesting, I got this wrong because in Excel 434115 = July 24th 3088, not the 26th. Why there is a difference of 2 days over a 1100+ year gap is beyond me. Sum of leap minutes?

  • Hiren Shah 3

    SSC-Addicted

    Points: 402

    Here is how i got it!!!

    select dateadd("d",'435365'-1250, cast (0 as datetime))

    Hiren

    Hiren Shah

    :kiss:KISS (Keep It Simple Stupid) :kiss:

  • SanjayAttray

    SSChampion

    Points: 13157

    Interesting question.

    SQL DBA.

  • Adam-424116

    Ten Centuries

    Points: 1240

    Dan Guzman - Not the MVP (11/18/2009)


    Interesting, I got this wrong because in Excel 434115 = July 24th 3088, not the 26th. Why there is a difference of 2 days over a 1100+ year gap is beyond me. Sum of leap minutes?

    This made me curious, so I decided to look into it. I copied the results of a query on my numbers table (converting each number to it's corresponding date) into Excel, and converted the "number" column to a date column. It appears that this inconsistency is due to two differences. The first is that in SQL 0=1900-1-1, but in Excel 1=1900-1-1. The second is that EXCEL incorrectly thinks 1900 was a leap year. So, after 59 (1900-03-01 in SQL, 1900-02-28 in EXCEL), all of the first 1,000,000 integers to dates are off by 2. (I didn't check beyond 1,000,000, but I'm guessing it holds true until other boundary conditions apply).

    So, I suppose this is a cautionary tale of how to transfer date/time data between systems!

  • john schroeder-334489

    Valued Member

    Points: 74

    Adam-424116 (11/18/2009)


    Dan Guzman - Not the MVP (11/18/2009)


    Interesting, I got this wrong because in Excel 434115 = July 24th 3088, not the 26th. Why there is a difference of 2 days over a 1100+ year gap is beyond me. Sum of leap minutes?

    This made me curious, so I decided to look into it. I copied the results of a query on my numbers table (converting each number to it's corresponding date) into Excel, and converted the "number" column to a date column. It appears that this inconsistency is due to two differences. The first is that in SQL 0=1900-1-1, but in Excel 1=1900-1-1. The second is that EXCEL incorrectly thinks 1900 was a leap year. So, after 59 (1900-03-01 in SQL, 1900-02-28 in EXCEL), all of the first 1,000,000 integers to dates are off by 2. (I didn't check beyond 1,000,000, but I'm guessing it holds true until other boundary conditions apply).

    So, I suppose this is a cautionary tale of how to transfer date/time data between systems!

    The same is true for MS Access 1=1900-1-1.

    See for an explanation of the Excel leap year error.

  • john.arnott

    SSChampion

    Points: 11882

    EXCEL incorrectly thinks 1900 was a leap year.

    Thank you, Adam-424116!

    This seemed so strange that I had to check and sure enough, even in Office 2008, Excel has 2/29/00, internally 60. I could hardly believe that MS hasn't considered this error, so checked their web site and found this "explanation" and instructions on adjusting dates before 1 March 1900 (http://support.microsoft.com/kb/214058/):

    When the date system in Microsoft Excel was originally created, it was designed to be fully compatible with date systems used by other spreadsheet programs.

    However, in this date system, the year 1900 is incorrectly interpreted as a leap year. Because there is no February 29 ("leap day") in the year 1900, the day of the week for any date before March 1, 1900 (the day after the "leap day"), is not computed correctly.

  • Tony++

    SSCarpal Tunnel

    Points: 4311

    As shown in this QOTD, adding/subtracting a number from a date is adding/subtracting that many whole days.

    Is this standard behavior, or is this undocumented behavior that could change in the future (to minutes/weeks/whatever)?

    I've wondered about this before, and so have always stuck with using DATEADD to be sure my code is safe.

Viewing 15 posts - 1 through 15 (of 17 total)

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