Mysql Code To Convert Excel Datetime

Excel's datetime values look like 42291.60493, which means MySQL sees them as strings and not as dates. Is there a MySQL code that can convert them to MySQL datetime? (i.e. like in

Solution 1:

I can think of 2 solutions:

1. Convert your dates within excel to a formatted date string that conforms to mysql's date and time format using text() function within excel.

2. Convert the number using calculation to date within mysql:

(the expression below may be simplified)

selectdate_add(date_add(date('1899-12-31'), interval floor(@datefromexcel) day), interval floor(86400*(@datefromexcel-floor(@datefromexcel))) second)

Solution 2:

Excel stores date times as the number of days since 1899-12-31. Unix stores the number of seconds since 1970-01-01, but only non-negative values are allowed.

So, for a date, you can do

select date_add(date('1899-12-31'), interval $Exceldate day )

This doesn't work for fractional days. But, for a unix date time, this would be nice:

select $ExcelDate*24*60*60 + unix_timstamp('1899-12-31')

But negative values are problematic. So, this requires something like this:

select ($ExcelDate - datediff('1899-12-31', '1970-01-01')) * 24*60*60

That is, just count the number of seconds since the Unix cutoff date. Note: this assumes that the date is after 1970-01-01, because MySQL doesn't understand unix dates before the cutoff.

Solution 3:

Excel date values represent the number of days from 1899-12-30. The reason for the odd value is that Excel needed to be compatible with Lotus 1-2-3 when it was introduced. Lotus 1-2-3 date difference calculations mistakenly assumed that 1900 was a leap year, adding an extra day. Microsoft replicated the bug for compatibility. (https://docs.microsoft.com/en-us/office/troubleshoot/excel/wrongly-assumes-1900-is-leap-year)

The calculation would be as @Shadow noted above using the correct value.