MySQL dates can’t be 0000-00-00
After a recent upgrade, some of my MySQL queries stopped working – where it was a DATE column with a default already set to 0000-00-00. That’s not allowed for MySQL v5.7.11 and above.
Not simple to change because the error keeps occuring.
Here’s how I got round it
UPDATE table SET column= "1000-01-01" WHERE column<"1000-01-01"; ALTER TABLE table MODIFY column DATE NULL; UPDATE table SET column = NULL WHERE column<"1000-01-01";
1000-01-01 is a valid date, so the first line converts those pesky 0000-00-00 to a valid date.
The second line will now work allowing NULL values
The third line makes those old 0000-00-00 values NULL.