How to set to NULL a datetime with 0000-00-00 00:00:00 value?
You need to first make the column nullable:
ALTER TABLE mytable MODIFY COLUMN field DATETIME NULL;
And then update the values:
UPDATE mytable SET field = NULL WHERE field = '0000-00-00 00:00:00';
From MySQL 5.7, SQL mode NO_ZERO_DATE makes this update impossible unless you firstly disable this restriction (for the duration of the transaction only).
SET sql_mode=(SELECT REPLACE(@@sql_mode,"NO_ZERO_DATE", ""));
UPDATE mytable SET field = NULL WHERE field = '0000-00-00 00:00:00';
You need to first make the column nullable:
@Mysql5.7
Wrong :
update episodes set `ending` = NULL WHERE `ending` = '0000-00-00'
Correct :
update episodes set `ending` = NULL WHERE `ending` = 0000-00-00
Note: Remove the quote from the date value in your where clause. Hope it help someone