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?

http://stackoverflow.com/a/13722614/1692632 – Darka Nov 21 '14 at 15:44 — Darka, Nov 21 '14 at 15:44

score 1 · Accepted Answer · answered Nov 24 '14 at 16:59

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.

answered Nov 24 '14 at 16:59

MonkeyPushButton

1,077
10
19

so are you saying that you can't properly import dates from excel into SQL Server, is there any workaround? – bernando_vialli Jul 11 '18 at 13:15
Workaround is as posted. Add two days for dates after 1900 and one day for dates before 1900 (if you have any). I am not aware of a cleaner fix. – MonkeyPushButton Sep 26 '18 at 10:13

Sql server and Excel float to date offset

1 Answers1

Linked