Converting a serial number into date

Question

Hallo friends,

In an Excel sheet I will receive the data, which in turn I need to upload it to sql server and then implement the logic.

I have received a date field --[Due Date] as number ex:-.40317. In Excel if you right click it and then format it to a date. It will show the correct date as 19.05.2010.

So after uploading the file as it is. I have used

SELECT (dateAdd(day,[Due Date],'1900-01-01')) FROM table1.

Assuming that Excel counts the days from 01-01-1900. So I am adding the number--[Due Date] Which should give me the proper formatted date..

But this returns me a value of 2010-05-21 00:00:00.000.

Can you please help me whether by assumption that Excel counts from 01-01-1900 is wrong..or the procedure that I have used is wrong to give such a value.

Answer 1

Excel provides functions which will convert dates to strings (try the functions list or Google). This should obviate the problem.

Answer 2

I asked a similar question a while ago - it wasn't talking about Excel but VB6, hence I'm not suggesting this question is a duplicate. However, the answer I think is the same.

Date 0 in SQL is 01/01/1900.
Date 0 in VB6 is 30/12/1899, 2 days earlier - that explains the difference.
So I believe Excel to be the same.

In Excel, I entered 0 in a cell and formatted the cell as a date - which displayed (weirdly IMO) 0/1/1900. I've had a quick look to find another reference that answers this definitively, but haven't managed to find one.

Edit:
Pulling in links that were given to me in my question (that do relate to Excel):
http://www.ozgrid.com/forum/showthread.php?t=46561
http://www.joelonsoftware.com/items/2006/06/16.html

Answer 3

As someone said, convert it to a better format in Excel first.

Most likely Excel and SQL Server have different takes on how many days there is from 1900-01-01. I think Excel had some old quirk with 1900 being a leap year or not (For 1-2-3 compatability?) so there's one of the extra days. The other I don't know, perhaps it starts counting on 1900-01-02 or Excel actually starts on 1900-01-00 or something? ^^