Welcome to Sign in | Help
in Search

datetime SQL vs Excel (why the difference?)

Last post 03-19-2010, 1:41 PM by darius2. 2 replies.
Sort Posts: Previous Next
  •  03-14-2010, 10:48 PM 8113

    datetime SQL vs Excel (why the difference?)

    select CAST(FLOOR(CAST(cast('2010-02-01' as datetime) AS FLOAT)) AS int) = 40208

    40208 in Excel = 01/30/2010
    Filed under: , , , ,
  •  03-15-2010, 11:01 AM 8114 in reply to 8113

    Re: datetime SQL vs Excel (why the difference?)

    Citat din 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.

    Vezi şi http://support.microsoft.com/kb/205719:

    In the Microsoft Office Spreadsheet Component, the value 0 evaluates to the date December 30, 1899 and the value 1 evaluates to December 31, 1899. This is different from Microsoft Excel. In Excel, the value 0 evaluates to January 0, 1900 and the value 1 evaluates to January 1, 1900. The date January 0, 1900 does not exist in the Microsoft Office Spreadsheet Component.

    In addition, the Office Spreadsheet Component does not recognize the year 1900 as a leap year. After March 1, 1900, however, dates in the Microsoft Office Spreadsheet Component and Microsoft Excel begin to match.

    Razvan
  •  03-19-2010, 1:41 PM 8118 in reply to 8114

    Re: datetime SQL vs Excel (why the difference?)

    mersi mult :)
View as RSS news feed in XML
Powered by Community Server (Commercial Edition), by Telligent Systems