How can I convert a OLE Automation Date value to a date in SQL Server

Question

My application stores dates as OLE Automation doubles with the DateTime.ToOADate() command.

I need to create a SQL view which shows me the Date stored. How can I quickly convert the double to a date?

Answer 1

Does

SELECT CAST(CASE WHEN OLEFLOAT > 0 THEN 
                         OLEFLOAT-2.0 
                 ELSE 
       2*CAST(OLEFLOAT AS INT) - 2.0 +  ABS(OLEFLOAT) END as datetime)

work? From here

An OLE Automation date is implemented as a floating-point number whose integral component is the number of days before or after midnight, 30 December 1899, and whose fractional component represents the time on that day divided by 24. For example, midnight, 31 December 1899 is represented by 1.0; 6 A.M., 1 January 1900 is represented by 2.25; midnight, 29 December 1899 is represented by -1.0; and 6 A.M., 29 December 1899 is represented by -1.25.

That sounds pretty much like the same system SQL Server uses when you cast a date as a float except the offset needed to be fiddled by 2 and for "negative" dates. SQL server will substract backwards. So -1.25 is 18:00 whereas for OLE that means 06:00.