Converting integer to datetime
-
Hi there,
When I convert an integer to a datetime value in Excel I get different result when I do the same in SQL Server. Here's an example:
38718 equals to '1/1/2006'. Excel agrees. But if i run the following statement I get a different result. Why?
SELECT
CAST(38718 AS DateTime) As DateFromInt
DateFromInt
-----------------------
2006-01-03 00:00:00.000
(1 row(s) affected)
-
You need to minus 2 days on the converted datetime value. We had this issue before. We had a C++ clients sends datetime in float format.
The reason is that in C++ and SQL server, 0 means different dates ( 2 days difference).
-
Actually things go pear-shaped around 28th Feb 1900. One (Visual Studio, Excel, Delphi, etc) see 1900 as a leap year and also have 29th Feb 1900, while SQL Server does not and goes from Feb 28th to Mar 1st.
A bit ridiculous really, especially for those of us who store our dates as floats. Invariably, responses to queries in this regard are met with "..but why are you storing dates as floats.." which drives me up the wall since it does not matter WHY we are doing so, what matters is that it SHOULD be consistent.
-
SQLServer is right about 2000 being a leap year and 1900 NOT.
Centuries must be divisible by 400 to be considred a leap year...
I thought we sorted this out back in 1999.....
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply