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

Answer

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.