3

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 MS SQL)

Community
  • 1
  • 1
LWC
  • 1,084
  • 1
  • 10
  • 28
  • 1
    I think you imported data from csv not excel. – Zafar Malik Oct 14 '15 at 12:17
  • How are you getting the data from Excel to MySQL? Might be easier to do the conversion to a standard datetime format in your export, using Excel itself. It'll probably be easiest, and it'll definitely be right. – Matt Gibson Oct 14 '15 at 12:22
  • I use a Microsoft Query with parameters (like the date) within Excel. I can reformat the date within as Excel till my heart's delight, but that's just for display within Excel. – LWC Oct 14 '15 at 13:15

3 Answers3

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.

Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
  • date_add() has a different syntax for mysql: date_add(date('1899-12-31'), interval $Exceldate day ) – Shadow Oct 14 '15 at 12:44
  • Plus there's an extra "(" in the last line. Alas, a little apology, I edited the OP to reflect I meant MySQL datetime, not Unix datetime. Can you edit the answer accordingly? – LWC Oct 14 '15 at 12:48
2

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)

select date_add(date_add(date('1899-12-31'), interval floor(@datefromexcel) day), interval floor(86400*(@datefromexcel-floor(@datefromexcel))) second)
Shadow
  • 33,525
  • 10
  • 51
  • 64
  • Great answer. If you need microseconds to round correctly, modify this to date_add(date_add(date('1899-12-31'), interval floor(@datefromexcel) day), interval floor(86400000000*(@datefromexcel-floor(@datefromexcel))) microsecond) – jdog Nov 04 '16 at 03:29
1

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://learn.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.

garlic_rat
  • 99
  • 6