convert Excel Date Serial Number to Regular Date

this actually worked for me

dateadd(mi,CONVERT(numeric(17,5),41869.166666666664)*1440,'1899-12-30')

(minus 1 more day in the date)

referring to the negative commented post

The marked answer is not working fine, please change the date to "1899-12-30" instead of "1899-12-31".

select dateadd(d,36464,'1899-12-30')

In SQL:

select dateadd(d,36464,'1899-12-30')
-- or thanks to rcdmk
select CAST(36464 - 2 as SmallDateTime)

In SSIS, see here

http://msdn.microsoft.com/en-us/library/ms141719.aspx

You can cast it to a SQL SMALLDATETIME:

CAST(36464 - 2 as SMALLDATETIME)

MS SQL Server counts its dates from 01/01/1900 and Excel from 12/30/1899 = 2 days less.

Related