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?

By the way, those are not Julian Dates, they are Excel Date Serial numbers. 35464 is BCE 4614 November 01 12:00:00.0 UT Tuesday in Julian date and CE 1958 September 18 00:00:00.0 UT Thursday in Modified Julian Date. See here for an explanation of Julian Dates: http://aa.usno.navy.mil/data/docs/JulianDate.php — William Salzman, Dec 13 '12 at 15:22

score 55 · Accepted Answer · edited Oct 22 '15 at 00:00

55

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

edited Oct 22 '15 at 00:00

Chris Schaller

13,704
3
43
81

answered Dec 12 '12 at 23:56

Nick.Mc

18,304
6
61
91

score 16 · Answer 2 · edited Jan 16 '15 at 07:58

16

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

edited Jan 16 '15 at 07:58

Vojtěch Dohnal

7,867
3
43
105

answered Oct 01 '14 at 20:16

Jacob Santiago

313
3
9

Ricardo Souza · Answer 3 · 2021-09-20T20:42:43.653

15

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.

edited Sep 20 '21 at 20:42

answered Jan 08 '15 at 22:01

Ricardo Souza

16,030
6
37
69

7

FYI: 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](https://support.microsoft.com/en-us/kb/214326) , [The real Story](http://www.joelonsoftware.com/items/2006/06/16.html) And, yes, the creator of Stack Overflow did have a huge role to play in the whole thing. – bp_ Dec 22 '15 at 14:58
I think this answer is a bit cleaner & the comment above giving a link to Joel's blog article is just insanely awesome! – kiltannen Sep 16 '21 at 20:54

Tim Lehner · Answer 4 · 2020-10-28T20:27:38.437

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

score 6 · Answer 5 · edited Sep 10 '14 at 06:51

6

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

edited Sep 10 '14 at 06:51

Rory McCrossan

331,213
40
305
339

answered Sep 10 '14 at 06:48

drinky

91
1
5

what does "mi" mean in the first part of the formula? – Nicolaesse Jan 11 '17 at 09:08
mi = minute as the datepart in the dateadd formula. https://msdn.microsoft.com/en-us/library/ms186819.aspx – drinky Jan 12 '17 at 21:40

Hadi · Answer 6 · 2020-03-01T17:47:16.900

SSIS Solution

The DT_DATE data type is implemented using an 8-byte floating-point number. Days are represented by whole number increments, starting with 30 December 1899, and midnight as time zero. Hour values are expressed as the absolute value of the fractional part of the number. However, a floating point value cannot represent all real values; therefore, there are limits on the range of dates that can be presented in DT_DATE. Read more

From the description above you can see that you can convert these values implicitly when mapping them to a DT_DATE Column after converting it to a 8-byte floating-point number DT_R8.

Use a derived column transformation to convert this column to 8-byte floating-point number:

(DT_R8)[dateColumn]

Then map it to a DT_DATE column

Or cast it twice:

(DT_DATE)(DT_R8)[dateColumn]

You can check my full answer here:

Is there a better way to parse [Integer].[Integer] style dates in SSIS?

score 1 · Answer 7 · answered Dec 08 '15 at 16:46

1

Found this topic helpful so much so created a quick SQL UDF for it.

CREATE FUNCTION dbo.ConvertExcelSerialDateToSQL
(
    @serial INT
)
RETURNS DATETIME
AS
BEGIN
    DECLARE @dt AS DATETIME
    SELECT @dt = 
        CASE
            WHEN @serial is not null THEN CAST(@serial - 2 AS DATETIME)
            ELSE NULL
        END
    RETURN @dt              
END
GO

answered Dec 08 '15 at 16:46

user692942

16,398
7
76
175

I use something similar, but I find that the input parameter as a float is much more general. – Tim Lehner Jan 31 '20 at 22:48

score 1 · Answer 8 · answered Mar 06 '18 at 17:14

I had to take this to the next level because my Excel dates also had times, so I had values like this:

42039.46406 --> 02/04/2015 11:08 AM
42002.37709 --> 12/29/2014 09:03 AM
42032.61869 --> 01/28/2015 02:50 PM

(also, to complicate it a little more, my numeric value with decimal was saved as an NVARCHAR)

The SQL I used to make this conversion is:

SELECT DATEADD(SECOND, (
                        CONVERT(FLOAT, t.ColumnName) - 
                        FLOOR(CONVERT(FLOAT, t.ColumnName))
                       ) * 86400,
               DATEADD(DAY, CONVERT(FLOAT, t.ColumnName), '1899-12-30')
              )

score 1 · Answer 9 · answered Jul 01 '19 at 00:39

In postgresql, you can use the following syntax:

SELECT ((DATE('1899-12-30') + INTERVAL '1 day' * FLOOR(38242.7711805556)) + (INTERVAL '1 sec' * (38242.7711805556 - FLOOR(38242.7711805556)) * 3600 * 24)) as date

In this case, 38242.7711805556 represents 2004-09-12 18:30:30 in excel format

score 0 · Answer 10 · answered Mar 27 '17 at 12:48

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

score 0 · Answer 11 · edited Aug 10 '17 at 20:30

You can do this if you just need to display the date in a view:

CAST will be faster than CONVERT if you have a large amount of data, also remember to subtract (2) from the excel date:

CAST(CAST(CAST([Column_With_Date]-2 AS INT)AS smalldatetime) AS DATE)

If you need to update the column to show a date you can either update through a join (self join if necessary) or simply try the following:

You may not need to cast the excel date as INT but since the table I was working with was a varchar I had to do that manipulation first. I also did not want the "time" element so I needed to remove that element with the final cast as "date."

UPDATE [Table_with_Date]
SET [Column_With_Excel_Date] = CAST(CAST(CAST([Column_With_Excel_Date]-2 AS INT)AS smalldatetime) AS DATE)

If you are unsure of what you would like to do with this test and re-test! Make a copy of your table if you need. You can always create a view!

score 0 · Answer 12 · edited Aug 04 '18 at 08:10

0

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
 )

edited Aug 04 '18 at 08:10

marc_s

732,580
175
1,330
1,459

answered Aug 03 '18 at 13:41

Selcuk Akbas

711
1
8
20

1

For 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 at `2018-09-03 16:23:29`) – justbeez Feb 26 '21 at 22:14

score 0 · Answer 13 · answered Sep 25 '20 at 10:20

0

For those looking how to do this in excel (outside of formatting to a date field) you can do this by using the Text function https://exceljet.net/excel-functions/excel-text-function

i.e.

A1 = 132134
=Text(A1,"MM-DD-YYYY") will result in a date

answered Sep 25 '20 at 10:20

Zr.Ms. Bruinvis

54
7

score -3 · Answer 14 · edited Jul 28 '14 at 16:47

-3

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

edited Jul 28 '14 at 16:47

V31

7,626
3
26
44

answered Jul 28 '14 at 16:22

user2788991

1
1

convert Excel Date Serial Number to Regular Date

14 Answers14

SSIS Solution

Linked

Related