How to set null in datetime from 0000-00-00 00:00:00?

Question

I need to change some values ​​in my DB.

I forgot to set the table to NULL and set the default to 00:00:00.

Now I need to convert this value to NULL .

The field type is Datetime.

How can i do this?

I am trying to use a typical Update table set field = NULL WHERE field = '0000-00-00 00:00:00'; but it does not work.

Answer 1

First you need to make the column zero:

 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'; 

Answer 2

Starting with MySQL 5.7, the SQL mode NO_ZERO_DATE makes this update impossible unless you first disable this restriction (only for the duration of the transaction).

 SET sql_mode=(SELECT REPLACE(@@sql_mode,"NO_ZERO_DATE", "")); UPDATE mytable SET field = NULL WHERE field = '0000-00-00 00:00:00'; 

Answer 3

Unfortunately this will not work.
per:

 update episodes set 'ending' = NULL WHERE 'ending' = '0000-00-00' 

If 0000-00-00 is in the where clause, you get an error:

Msgstr "1292 - Invalid date and time value:" 0000-00-00 "for a column ending in row 1".

even if ending has NULL and defalut=null in the field definition.

Answer 4

First you need to 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 the where clause. Hope this helps someone