CODEWITHSUNDEEP
sql-serverdatetimesql-convert
PRIME
PRIME

Reputation: 83

Wrong conversion to datetime in SQL Server

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.

Upvotes: 1

Views: 898

Answers (1)

John Cappelletti
John Cappelletti

Reputation: 82010

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

Upvotes: 2

Related Questions