Excel dates differ from SQL dates

[Martin Ryan]

One of those things that you never need to know till it trips you up. The integer representation of a date differs between Excel/Access and SQL. Eg 3 November 2008 in Excel is 39755 but in SQL its 39753.

There are two reasons for this, one is because Excel starts counting on 0 Jan 1900 while SQL starts on 1 Jan 1900. Two is because a very early version of Excel had an error in the calculations for leap years: it mistakenly assumed that 1900 was one, whereas in reality, it
wasn't. They could not fix this in later versions, because that would break compatibility.

Probably a useless fact, but I thought I'd share it anyway as it almost had me sending an accusatory to a user implying imbecility.

Martin

[Alan Kirk]

One of those things that you never need to know till it trips you up. The integer representation of a date differs between Excel/Access and SQL. Eg 3 November 2008 in Excel is 39755 but in SQL its 39753.

There are two reasons for this, one is because Excel starts counting on 0 Jan 1900 while SQL starts on 1 Jan 1900. Two is because a very early version of Excel had an error in the calculations for leap years: it mistakenly assumed that 1900 was one, whereas in reality, it
wasn't. They could not fix this in later versions, because that would break compatibility.

Actually you can't blame Microsloth for that one. The original error was in Lotus 1-2-3, which had a stranglehold on the market at that time. To ensure that Excel worksheets were compatible with Lotus 1-2-3 ones (which of course mattered greatly when MS were trying to get people to migrate), they had to implement the same bug otherwise a whole bunch of date calculations would be screwed up when you opened a Lotus workbook (of which there were many at the time) in Excel.

Now of course if you have a workbook which is shared with a Macintosh user who uses the 1904 date system (Tools -> Options -> Calculation), you ALSO have to factor in...