mysql 0000-00-00 update error

Question

I am trying to update a my date field to null instead of 0000-00-00, but I errors.

 ERROR 1292 (22007): Incorrect date value: '0000-00-00' for column 'rel_date' at row 1

However I can update the records individually?

 update table set rel_date = NULL where id = 12;

But if I try

update table set rel_date = NULL where rel_date like '%0000%'
or
update table set rel_date = NULL where rel_date = '0000-00-00'
I get ERROR 1292 (22007): Incorrect date value: '0000-00-00' for column 'rel_date' at row 1

schema

id              | int(10) unsigned | NO   | PRI | NULL    | auto_increment |
| document_id   | varchar(40)      | NO   | MUL | NULL    |                   |
| revision_date | date             | YES  |     | NULL    |                |

Show your database schema. The column probably is not nullable. — aynber, Dec 18 '17 at 19:27
Also check `SELECT @@SQL_MODE`. Recent MySQL versions default to a `NO_ZERO_DATE` mode. — Michael Berkowski, Dec 18 '17 at 19:32
You really don't want zero dates in your database, so MySQL is just looking out for you here. If you need to purge bad data, try switching to `VARCHAR(255)`, cleaning it, then flipping back to `DATE`. — tadman, Dec 18 '17 at 19:34
Yes the NO_ZERO_DATE is set. Do i just set sql_mode session to nothing. Then run my update. — user3525290, Dec 18 '17 at 19:38

score 3 · Answer 1 · answered Dec 18 '17 at 19:48

3

SET sql_mode=(SELECT REPLACE(@@sql_mode,'NO_ZERO_DATE',''));

Run update command. close instance of mysql or probably add back to current session.

SET sql_mode=(SELECT CONCAT(@@sql_mode,',NO_ZERO_DATE'));

answered Dec 18 '17 at 19:48

user3525290

1,557
2
20
47

score 2 · Answer 2 · answered Oct 30 '18 at 20:13

2

I had the same problem. If you have a minimum valid date in your table, suppose it is '1950-01-01', then you could do:

update table set rel_date = NULL where rel_date < '1950-01-01'

answered Oct 30 '18 at 20:13

atilauy

31
2

1

thank you ..you saved my life... instead of `rel_date='0000-00-00'`, i use this `rel_date < '0001-01-01'`...and it's working – Syamsoul Azrien Jan 08 '21 at 22:57

mysql 0000-00-00 update error

2 Answers2