MySQL reads Date columns in Excel as DateTime?

Question

I'm trying to export Excel sheets to MySQL as new table using the plugin.

The export fails on "MySQL error 1064" when I tried to change the data type to Date.

I have a few columns in Excel that are formatted as Short Date, although some of them are generated by functions like =TODAY(), some are numbers like "42445", etc.

But all of them date type not Date Time.

For example it bothers me that MySQL detects '1/1/2016' in Excel as '1/1/2016 12:00:00 AM' as VarChar(225). And whenever I changed the type to Date the error message mentioned above appears.

I did try to re-format the columns in spreadsheet to YYYY-MM-DD but MySQL always convert them to MM/DD/YYYY HH:MM:SS

Thanks in advance for any help.

MySQL for Excel 1.3.6

Office 2016 32-bit

edit: this looks more like the plugin's bug of this version https://bugs.mysql.com/bug.php?id=80079

Answer 1

Kindly Note that MySQL stores date in YYYY-MM-DD format by default.

One way is to convert all dates into YYYY-MM-DD format so that it will be compatible with MYSQL.

We can format the date column in excel before exporting it to MYSQL by following the below steps:

1. Select the column which contains the date
2. Right click and select format cells
   i. Choose date under category(on left-hand side)
   ii. Then choose custom under category
   iii. Under type insert the format --> YYYY-MM-DD
   You will see all the dates in the columns get converted into the format --> YYYY-MM-DD

Then you can import the date into MYSQL safely.

Another way is to keep the column datatype as VARCHAR and use TO_DATE() function to parse the string data into DATE format.

Hope this solves your problem !!

• cxwf: Hi Bibhuti, thanks for the reply. I tried that before and MySQL always convert them to mm/dd/yyyy no matter what formats are used in Excel
• Bibhuti Bhusan Padhi: So are you still facing the same issue?
• cxwf: yes. see my edit. it could be 1.3.6's bug
• user161095: If you also need time: YYYY-MM-DD hh:mm:ss