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.

Answer 1

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