convert decimal serial number from excel to mysql correctly

Question

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

MySQL dates use YYYY-MM-DD format by default. You can format differently with the [DATE_FORMAT()](https://dev.mysql.com/doc/refman/5.7/en/date-and-time-functions.html#function_date-format) function. — Bill Karwin, Aug 28 '17 at 16:34
Why do you think the value is off by two days? When I try the MySQL query `select to_days('2017-08-28') - to_days('1900-01-01');` I get 42973. — Bill Karwin, Aug 28 '17 at 16:35
It add 2 more days to the result. 8/26 and 8/28. So I am not sure if the query would produce incorrect results for the whole database — Hai Vu, Aug 28 '17 at 16:36
how about trying to convert it to the proper date/time format in Excel before saving to a csv? — Scott Craner, Aug 28 '17 at 16:37
Ah, I think I have found the issue: Excel datetime values count 1900-01-01 as the value 1, not 0. And Excel has a bug that it believes 1900 is a leap year. That accounts for the two days. https://stackoverflow.com/questions/981655/how-to-represent-a-datetime-in-excel — Bill Karwin, Aug 28 '17 at 16:37
Ahh, I see. So I just need to subtract 2 days to find the correct answer. Thank you! — Hai Vu, Aug 28 '17 at 16:41

score 4 · Accepted Answer · answered Aug 28 '17 at 16:44

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.

1 Answers1