MySQL reads Date columns in Excel as DateTime?

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

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 !!