11

I have found something really weird today while doing a work converting a datetime to text in excel and using the number generated by it to convert to datetime in SQL Server.

What is weird about it? Different results. Two days difference to be precise.

I assumed the date of today (20/05/2014 dd/MM/yyyy ) in Excel and got 41779 as result in text.

Datetime input

Convert to text

I got the text value and I use SQL convert to datetime to retrieve the value as date and I did not get the result I wanted.

SQL Convert to datetime

I even tested with datetime2 but I learned that I can't convert int to datetime2

enter image description here

I'm not a MS Excel expert nor a SQL Server expert, but what is going on? I can make it work by doing the number generated by MS Excel and removing 2, but still doesn't make sense to me.

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
André Silva
  • 1,149
  • 9
  • 30
  • hehe compare `Select Cast(0 as DateTime)` vs. `=DATEVALUE("1900-01-01")` and [**`find the one extra leap year`**](http://www.joelonsoftware.com/items/2006/06/16.html) –  May 20 '14 at 12:43
  • That is a difference of 1, but why is there a two day difference between those tests? – André Silva May 20 '14 at 12:44
  • 1
    Now that I saw your edit it makes sense. But really Microsoft? Damn. If you could post it as an awnser I'll mark as answer. Thanks. – André Silva May 20 '14 at 12:48
  • 2
    to be technically correct it's not microsoft's bug. It was IBMs bug which Microsoft kind of "inherited" and was aware off but wanted the ability to convert the Lotus spreadsheets, see a [better explanation](http://www.joelonsoftware.com/items/2006/06/16.html) –  May 20 '14 at 13:06

3 Answers3

12

Concentrating specifically on DATETIME, where the casts from int are allowed, there are two reasons for the discrepancy.

  1. Excel uses a base of 1 for dates, SQL Server uses 0, i.e. 01/01/1900 when converted to a number in excel is 1, however, in SQL it is 0: SELECT CAST(CAST('19000101' AS DATETIME) AS INT); Will give 0.

  2. There is a deliberate error in excel to allow portability from Lotus where the bug was not deliberate*. Excel considers 29th February 1900 a valid date, but 1900 was not a leap year. SQL does not have this issue, so this means there is an extra day in the excel calendar.

*(further reading on this suggests it might have been deliberate, or considered inconsequential)

ADDENDUM

There is a Microsoft Support Item that sates:

When Lotus 1-2-3 was first released, the program assumed that the year 1900 was a leap year, even though it actually was not a leap year. This made it easier for the program to handle leap years and caused no harm to almost all date calculations in Lotus 1-2-3.

When Microsoft Multiplan and Microsoft Excel were released, they also assumed that 1900 was a leap year. This assumption allowed Microsoft Multiplan and Microsoft Excel to use the same serial date system used by Lotus 1-2-3 and provide greater compatibility with Lotus 1-2-3. Treating 1900 as a leap year also made it easier for users to move worksheets from one program to the other.

Community
  • 1
  • 1
GarethD
  • 68,045
  • 10
  • 83
  • 123
11

hehe ;) one day ages ago I wondered the same thing... do a simple exercise:

compare Select Cast(0 as DateTime) vs. =DATEVALUE("1900-01-01") which explains 1 day difference

and find the one extra leap year by reading the father of VBA, Joel Spolsky, explanation

tl;dr

check out the difference - which exlpains the 2nd day

=DateValue("1900-02-28") and =DateValue("1900-03-01")

-1

I search this website and found solution look at code here. You need to do a check for any number under 60, if it is under 60 do not subtract 1, because you are not in the bug yet. If it is over you must subtract one from the date because of the 02/29/1900 bug

DECLARE @days INT = 41779

DECLARE @StartDate DATETIME = '1899-12-31'

if (@days > 59) SET @days = @days -1 ;

SELECT DATEADD(day,@days,@StartDate)

This is where I found solution https://stackoverflow.com/a/727495/1692632

Community
  • 1
  • 1
Darka
  • 2,762
  • 1
  • 14
  • 31
  • 1
    Darka, no. There are 2 answers different from yours which explain the actual difference. –  May 20 '14 at 12:54
  • it is same as this one – Darka May 20 '14 at 12:55
  • Darka, this is an SQL based workaround to return the same date as Excel but it does not address the original question. –  May 20 '14 at 13:15