MySQL reads Date columns in Excel as DateTime

excelMySQLplugins

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

Best Answer

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

Related Question