Casting a double to a DateTime type is off by 2 days?

I store datetime values in the database as sql float type (Converted from an DateTime.OADate) for a myriad of reasons however in certain circumstances it is nice to get a human readable date/time column back from the database. I have found that I can execute the statement

SELECT CAST (timerecorded_utc as DATETIME) FROM tablename

and it will give me the date time string I am looking for but it seems to be of开发者_运维技巧f by exactly 2 days. I realize I can just modify the statement (since in time represented as a double 1 day = 1.0) to be

SELECT CAST (timerecorded_utc-2.0 as DATETIME) FROM tablename

BUT I was wondering if this is consistent AND it seems to me there is some reason for the discrepancy that I am missing.

It's because the epochs the dates use are different.

SQL Server's DATETIME uses 01/01/1900 00:00:00 as the epoch, which you can see by running the following query: SELECT CAST(0 AS DATETIME)

OADate is a bit odd, as it could have an epoch of 30/12/1899 00:00:00 or 31/12/1899 00:00:00 depending on whether you believe the Visual Basic or Excel guys, respectively. It would appear that from your two day difference, the .NET version goes with the 30th.

So, epoch off by two days gives two days difference in the outcome when you convert between the two types of date via a raw number.

Epic Epochs... Here is my TSQL solution in SQL Server using the built in "DateAdd" function:

Select DateAdd(DAY, cast([ENDING DATE] as decimal(10,0)), '12/30/1899')
from YourTable

In my case I was importing a string saved in Excel via C# Core App and uploading to a SQL Server database so my [Ending Date] is a string which I casted as a decimal with no precision as I only needed the actual date, and not the time of day. As @GregBeech mentioned, your base date might be '12/31/1899'.