convert Excel Date Serial Number to Regular Date
Solution 1
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
Solution 2
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')
Solution 3
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.
Solution 4
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
Solution 5
tldr:
select cast(@Input - 2e as datetime)
Explanation:
Excel stores datetimes as a floating point number that represents elapsed time since the beginning of the 20th century, and SQL Server can readily cast between floats and datetimes in the same manner. The difference between Excel and SQL server's conversion of this number to datetimes is 2 days (as of 1900-03-01, that is). Using a literal of 2e
for this difference informs SQL Server to implicitly convert other datatypes to floats for very input-friendly and simple queries:
select
cast('43861.875433912' - 2e as datetime) as ExcelToSql, -- even varchar works!
cast(cast('2020-01-31 21:00:37.490' as datetime) + 2e as float) as SqlToExcel
-- Results:
-- ExcelToSql SqlToExcel
-- 2020-01-31 21:00:37.490 43861.875433912
Comments
-
Sreedhar over 2 years
I got a column called DateOfBirth in my csv file with Excel Date Serial Number Date
Example:
36464 37104 35412
When i formatted cells in excel these are converted as
36464 => 1/11/1999 37104 => 1/08/2001 35412 => 13/12/1996
I need to do this transformation in SSIS or in SQL. How can this be achieved?
-
bp_ over 8 yearsFYI: the 2 days less issue is much more fun than your incorrect assessment. Mr. Gates himself approved the date oddity. See these two resources: MS Support , The real Story And, yes, the creator of Stack Overflow did have a huge role to play in the whole thing.
-
Nicolaesse over 7 yearswhat does "mi" mean in the first part of the formula?
-
drinky over 7 yearsmi = minute as the datepart in the dateadd formula. msdn.microsoft.com/en-us/library/ms186819.aspx
-
Tim Lehner over 4 yearsI use something similar, but I find that the input parameter as a float is much more general.
-
justbeez about 3 yearsFor people landing here and looking for a more brief solution for BigQuery, try
SELECT DATETIME_ADD("1899-12-30",INTERVAL CAST(43909.91492 * 86400 AS INT64) SECOND)
(also, the answer above seems to be off by one second; both Excel and my code put that serial at2018-09-03 16:23:29
) -
kiltannen over 2 yearsI think this answer is a bit cleaner & the comment above giving a link to Joel's blog article is just insanely awesome!