Why does SQL calc float dates different than Excel

[bwestover]

If I enter the value 39301.5 into a cell and format it as date, I get the following result:
08/07/2007 12:00 PM

However, if I put 39301.5 into this statement in SQL:

Select cast(39301.5 as datetime)

I get the following result:
2007-08-09 12:00:00.000

Its off by 2 days... Whats up with that?

I only ask because I have another app (not excel) that is working like excel and I need it to work like SQL. I know I could simply use DATEADD to adjust the output by 2 days... but I really wonder whats going on.

[bwestover]

Ok, so I figured out why excel reads 2 days off of MS SQL.

The first day is lost because Excel starts counting from 1, rather than 0. Enter a 0 into excel and have it convert it to a date and you get 1/0/1900. This isn't a real day, so it is assumed that Excel really starts its counting on 12/31/1899.

The second day is lost because of a bug between Microsoft and Pope Gregory the 8th. In 1582 the Roman Calendar was revised (and subsequently named the Gregorian calendar). One of the new revisions was that every 100 years there would not be a leap day. Enter 60 into excel and you'll get 2/29/1900. This day doesn't exist in the gregorian calendar.

So, since SQL starts with day 0 equaling 1/1/1900 and doesn't contain the extra fictitious day they are off by 2 days from each other.

[teddarr]

Hmm. I knew about the 1st error, wasn't aware of the second. Thanks for posting your discovery. Please continue to do so.