Search code examples
mysqlexceldatetimetypeconverter

convert decimal serial number from excel to mysql correctly

I have a column in Excel csv with values like this

42973.74257

I want to convert it to MySQL date time by using:

DATE_ADD('1900-01-01', INTERVAL (42973.74257*60*60*24) second) as date

However, it would give me an incorrect result

2017-08-28 17:49:18.048000

while the correct result should be

8/26/2017 5:49:18 PM

It add 2 more days to the result. As I have around 1M rows so I would not know if all the results would be incorrect.

Do you guys have any better way to convert serial numbers from excel to mysql?

Thanks,

H

Solution

  • Your answer is wrong by two days.

    One of those days is because it actually treats 1st of Jan 1900 as 1, not 0 (ref).

    According to this blog post, it's off by another day because Excel incorrectly treats the year 1900 as a leap year for legacy compatibility reasons.

    If you subtract 2 from the value before passing it into your formula, it should work.