Bug #77722 Problems with DATE in Excel and the UPDATE query passed to MySQL
Submitted: 14 Jul 2015 21:00 Modified: 15 Jul 2015 14:46
Reporter: Brandon Osborn Email Updates:
Status: Not a Bug Impact on me:
Category:MySQL for Windows: MySQL for Excel Severity:S2 (Serious)
Version:1.3.4 OS:Microsoft Windows (8.1 PRO)
Assigned to: CPU Architecture:Any
Tags: date, Excel, format, MySQL, warnings

[14 Jul 2015 21:00] Brandon Osborn
My MySQL 'activities' table has a field called 'start_date' and some other text fields.

I changed my Windows default short-date format to yyyy-MM-dd to match Excel's date format with MySQL's date format. Before I did this, none of the 'start_date' UPDATEs worked at all, no matter what I did. The connection and import of table data using MySQL for Excel Edit Data works fine.

Now that I've matched up the DATE formats, here's what I get:

If the 'start_date' field in the database is formatted as a DATE, I get the following warning upon COMMIT, and the UPDATE fails:

The server returned 1 warning(s):
001: Query did not match any rows in the DB, values in the row 74 may have changed; please refresh values from the DB.
Deleted 0, Inserted 0, Updated 0 row(s).

If the 'start_date' field in the database is formatted as a VARCHAR(10), I get the following, and the UPDATE succeeds:

The server returned 1 warning(s):
001: 1265 - Data truncated for column 'start_date' at row 1 (Excel row: 74).
Deleted 0, Inserted 0, Updated 1 row(s).

How to repeat:
In this case, I'm using XAMPP with MySQL Workbench, etc.

Create a table 'activities' with a field 'start_date' DATE
insert a few dates like '2010-01-01', '2014-02-05', etc
Launch Excel, launch the MySQL for Excel app
Choose the db & table
Click on "Edit Table Data"
When you see the "Revert" and "Commit" buttons, change a date or 2.
Commit the data
It will fail with warnings
ALTER the DATE field to be VARCHAR(10)
Close Excel (I don't know if that's necessary, but I did it to make sure.)
Go through secion B: as you did before.
The UPDATE will succeed with warnings.

Suggested fix:
The problem with Excel is that it has a date bug. When it sees something that looks like a date, it converts it to a date, even if it's a string like "Oct51" or '2015-01-02'. Microsoft's Office support documentation says that using an apostrophe will force a string to remain a string, but this is demonstrably wrong. This has been a thorn in my brain for years - MS refuses to address, much less fix it.

Anyway, my suggested fix would be to parse anything date to a string before it's passed to the MySQL server. For a lot of people who shuffle data back & forth between Excel and MySQL, you will be AN IMMORTAL HERO if you fix this.
[15 Jul 2015 14:46] MySQL Verification Team
We're sorry, but the bug system is not the appropriate forum for asking help on using MySQL products. Your problem is not the result of a bug.

Support on using our products is available both free in our forums at http://forums.mysql.com/ and for a reasonable fee direct from our skilled support engineers at http://www.mysql.com/support/

Thank you for your interest in MySQL.