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.


Leave a Reply