Wrong conversion to datetime in SQL Server

Question

In Excel, when I convert these 'General' numbers to dates, which is what they really are, I get different numbers than when I CONVERT() them in SQL Server. The data is coming from an import.

General     Excel conversion to date       SQL Server conversion to datetime
37621       31-12-2002                     2003-01-02 00:00:00.000
39082       31-12-2006                     2007-01-02 00:00:00.000
39447       31-12-2007                     2008-01-02 00:00:00.000
etc.

How can I get the real dates as in Excel with a query in SQL Server? As mentioned, I already used CONVERT(datetime, [General]), but then I get the outcomes as in the column SQL Server conversion to datetime.

What is the `convert()` code you are using in SQL Server? Also where is the data coming from? A table? An import? — ollie, May 31 '17 at 19:56
Excel/Windows and SQL Server have different Day-Zeroes. Off by two days. IIRC, this was an original mistake in Windows V1 (I suspect it was actually two different mistakes combined). — RBarryYoung, May 31 '17 at 20:16

score 2 · Answer 1 · answered May 31 '17 at 19:59

2

To convert an Excel value to a date

Select DateAdd(DAY,General,'1899-12-30')
 From  YourTable

Demonstration

Declare @YourTable Table ([General] int,[Excel conversion to date] varchar(50))
Insert Into @YourTable Values
 (37621,'31-12-2002')
,(39082,'31-12-2006')
,(39447,'31-12-2007')

Select *
      ,DateAdd(DAY,[General],'1899-12-30') 
from @YourTable

Returns

General Excel conversion to date    (No column name)
37621   31-12-2002                  2002-12-31 00:00:00.000
39082   31-12-2006                  2006-12-31 00:00:00.000
39447   31-12-2007                  2007-12-31 00:00:00.000

answered May 31 '17 at 19:59

John Cappelletti

79,615
7
44
66

John, I appreciate your answer. But it does not seem to work for me when I use the SELECT statement above..I only need to replace 'General' with my real column name right? – PRIME May 31 '17 at 20:55
@PRIME Correct. It was to demonstrate how convert an Excel Date Value into a SQL Server Date via the DateAdd() – John Cappelletti May 31 '17 at 20:59
@JohnCappelletti why 1899-12-30? – Ankit Suhail Jun 02 '17 at 13:47
@AnkitSuhail Take a peek at https://stackoverflow.com/questions/3963617/why-is-1899-12-30-the-zero-date-in-access-sql-server-instead-of-12-31 – John Cappelletti Jun 02 '17 at 13:50
@JohnCappelletti Thanks. it's Quite interesting. Thanks again. – Ankit Suhail Jun 02 '17 at 14:09

Wrong conversion to datetime in SQL Server

1 Answers1