How to import Excel's serialized datetime into MySQL
datetime, excel, mysql, serialization, sql
Solution
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.
Problem
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!