Cast as datetime question

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

  • 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/
  • 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.

  • 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

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

    :unsure:

  • 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

  • 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

  • :blush:

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

  • 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?

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

  • Interesting question.

    SQL DBA.

  • 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!

  • 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.

  • 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.

  • 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 16 total)

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