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

Question

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.

score 14 · Accepted Answer · answered Sep 28 '15 at 18:36

14

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

answered Sep 28 '15 at 18:36

Mureinik

297,002
52
306
350

Nullable is setted in symfony2 – Borja Pombo Sep 28 '15 at 18:42
1

Look at answer from @AlterPHP for MySQL 5.7 – Lorenz Meyer Jan 17 '19 at 13:43

score 11 · Answer 2 · edited Jan 17 '19 at 13:42

11

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

edited Jan 17 '19 at 13:42

Lorenz Meyer

19,166
22
75
121

answered Nov 27 '18 at 17:21

AlterPHP

12,667
5
49
54

score 4 · Answer 3 · edited Feb 07 '19 at 07:54

4

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

edited Feb 07 '19 at 07:54

Michaël Azevedo

3,874
7
31
45

answered Feb 07 '19 at 07:32

A. Sideeq

57
1
3

are you sure? I mean are you sure it's not the same (just in case it is working)? :) – alexbusu Oct 08 '20 at 14:19
What mysql version are you using – A. Sideeq Oct 09 '20 at 16:24
Since dates in databases are stored as integers, you can simply set `UPDATE episodes SET ending = NULL WHERE ending = 0;` – MichielB Jan 17 '23 at 10:03

score 0 · Answer 4 · edited Jun 04 '21 at 08:19

0

Unfortunately this will not work.
for:

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

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

"1292 - Incorrect datetime value: '0000-00-00' for column 'ending' at row 1".

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

edited Jun 04 '21 at 08:19

Jan Pfeifer

2,854
25
35

answered Feb 04 '18 at 16:26

William Drescher

1

This is an older question: If your answer is due to using a much newer version of mySQL software than state version number in your answer. Now it looks silly because the correct/best answer was given more than2 years ago. – ZF007 Feb 04 '18 at 17:07

score 0 · Answer 5 · answered Dec 23 '20 at 03:11

0

What worked for me is:

Set the field from datetime to varchar(191).
Update all rows with field = '0000-00-00 00:00:00' to NULL.
Set the field back to datetime.

answered Dec 23 '20 at 03:11

JuliaJ

79
7

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

5 Answers5

Linked