Description:
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.
A:
Create a table 'activities' with a field 'start_date' DATE
insert a few dates like '2010-01-01', '2014-02-05', etc
B:
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
C:
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.
