How to import Excel's serialized datetime into MySQL

Question

How do I import and preserve date format fields such as "8/21/2012" from Excel to MySQL?

I am using MySQL Workbench and the Excel MySQL Excel data transfer plug in. When I select the Excel data I want to import into MySQL, I get a window where I declare variable types for all fields. All fields and declarations work as expected except for date and time fields. Both date and time switch from 8/21/2012 to a number like 398475 etc. How can I import these fields into MySQL by preserving the dashed mm/dd/yyyy format? I assume the same procedure will work for time as well.

Alternatively, is there a way to convert the serialized datetime value (a float, representing the number of days since 1/1/1900) back to mm/dd/yyyy in MySQL ?

Thank you!

Solution 1

You can convert your date cells into a MySQL supported format using the TEXT function.

=TEXT(A1,"YYYY-MM-DD")

This will convert a date in cell A1 to the yyyy-mm-dd format that the MySQL date field expects.

Solution 2

To get the serialized date format (say 36422) into a useful date format in MYSQL use the interval function added to the base date that Excel uses (which is actually 1900-00-00 but since that doesn't exist you will have to use 1900-01-01 which is why we subtract 2 from the date column)

`'1900-01-01' + INTERVAL(Your_Date_Column - 2)DAY`