convert Excel Date Serial Number to Regular Date

Question

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?

Answer 1

Google BigQuery solution

Standard SQL

Select Date, DATETIME_ADD(DATETIME(xy, xm, xd, 0, 0, 0),  INTERVAL xonlyseconds SECOND) xaxsa
from (
  Select Date, EXTRACT(YEAR FROM xonlydate) xy, EXTRACT(MONTH FROM xonlydate) xm, EXTRACT(DAY FROM xonlydate) xd, xonlyseconds
  From (
     Select Date
        , DATE_ADD(DATE '1899-12-30', INTERVAL cast(FLOOR(cast(Date as FLOAT64)) as INT64) DAY )   xonlydate
        , cast(FLOOR( ( cast(Date as FLOAT64) - cast(FLOOR( cast(Date as FLOAT64)) as INT64)  ) * 86400 ) as INT64) xonlyseconds
     FROM (Select '43168.682974537034' Date) -- 09.03.2018  16:23:28
   ) xx1
 )

Answer 2

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

Answer 3

Try the approach discussed in the following link, which involves creating a function that performs the converstion and applying the function in your SQL.

http://www.ehow.com/how_12073756_convert-julian-date-sql.html

Answer 4

This worked for me because sometimes the field was a numeric to get the time portion.

Command:

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

Answer 5

In addition of @Nick.McDermaid answer I would like to post this solution, which convert not only the day but also the hours, minutes and seconds:

SELECT DATEADD(s, (42948.123 - FLOOR(42948.123))*3600*24, dateadd(d, FLOOR(42948.123),'1899-12-30'))

For example

• 42948.123 to 2017-08-01 02:57:07.000
• 42818.7166666667 to 2017-03-24 17:12:00.000

Answer 6

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')