CODEWITHSUNDEEP
mysqlsqlmysql-error-1292
user3525290
user3525290

Reputation: 1607

mysql 0000-00-00 update error

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

Upvotes: 1

Views: 3178

Answers (2)

atilauy
atilauy

Reputation: 41

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'

Upvotes: 2

user3525290
user3525290

Reputation: 1607

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

Upvotes: 3

Related Questions