Sql server and Excel float to date offset

Question

I'm working with date and float conversion on sql server and excel.

This is my query:

select getdate(),convert(float, getdate())

I get:

2014-11-21 16:38:49.973     41962,6936339506

If I copy this float number to Excel and I change cell type as date I get this value:

19-nov-2014

What's that? Why there is an offest of two days?

Answer 1

SQL server simply calculates the conversion of a date time to a float as the number of days since midnight on 01-Jan-1900 (i.e. select convert(DATETIME, 0) gives 1900-01-01 00:00:00.000)

Excel calculates a similar number, but the zero date is "00/01/1900". This is probably related to the fact that excel uses one based indexing, rather than the more common zero based indexing. The second day of difference comes from a well known bug whereby excel considers 1900 to have been a leap year.

Takeaway message: if you assume that excel is always behind by two days you'll be ok, except for dates on or before the 28th of February 1900.