Post reply

Cast as datetime question

  • November 17, 2009 at 9:31 pm

    #385807

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

  • November 18, 2009 at 3:48 am

    #1080781

    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/

  • November 18, 2009 at 5:34 am

    #1080808

    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.

  • November 18, 2009 at 5:35 am

    #1080810

    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

  • November 18, 2009 at 6:53 am

    #1080857

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

    :unsure:

  • November 18, 2009 at 7:23 am

    #1080889

    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

  • November 18, 2009 at 7:24 am

    #1080892

    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

  • November 18, 2009 at 7:36 am

    #1080903

    :blush:

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

  • November 18, 2009 at 9:19 am

    #1080987

    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?

  • November 18, 2009 at 9:29 am

    #1080997

    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:

  • November 18, 2009 at 9:54 am

    #1081021

    Interesting question.

    SQL DBA.

  • November 18, 2009 at 1:55 pm

    #1081198

    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!

  • November 18, 2009 at 3:03 pm

    #1081245

    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.

  • November 18, 2009 at 4:58 pm

    #1081300

    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.

  • November 19, 2009 at 7:38 am

    #1081563

    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