phpprogrammer phpprogrammer - 2 months ago 10
MySQL Question

How to set to NULL a datetime with 0000-00-00 00:00:00 value?

I need to change a few values on my DB.

I forgot to set nullable to the table and it set to 0000-00-00 00:00:00 by default.

Now I need to convert that value in

NULL
.

The field type is Datetime.

How can I do it?

I try with the typical
Update table set field = NULL WHERE field = '0000-00-00 00:00:00';
but it doesn't work.

Answer

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