"1292 Incorrect datetime value" when updating an "empty" datetime field

Question

I have an updated_at field that was not initially set with default datetime value on the server. For a short period, a bunch of records got inserted with an "empty" value and are showing up at 0000-00-00 00:00:00 in the updated_at column. I wrote am update command to copy the values of the created_at field to updated_at and it is updating the field correctly, however, when I run it in MySQL Workbench, I get the following warning:

6 row(s) affected, 1 warning(s): 1292 Incorrect datetime value: '' Rows matched: 6  Changed: 6  Warnings: 1

My query:

UPDATE mytable
SET updated_at=created_at
WHERE updated_at='' and id >0;

I'm not sure where that warning is coming from since the created_at times are all valid and the updated_at fields are being set with the correct data.

Answer 1

Congratulation on your commitment to clean dates.

Temporary disabling the NO_ZERO_DATE sql_mode will enable this cleanup.

Just to be sure, use 0000-00-00 00:00:00 as the date too.

SET @@SQL_MODE = REPLACE(@@SQL_MODE, 'NO_ZERO_DATE', '');
UPDATE mytable
SET updated_at=created_at
WHERE (updated_at='' OR updated_at='0000-00-00 00:00:00') and id >0;

Answer 2

There may be some NULL and 0000-00-00 00:00:00 values in created_at that's why it's showing 1 warning. You can cross check it with the below query :

select created_at , updated_at
from mytable
where ( (updated_at='' OR updated_at='0000-00-00 00:00:00') OR
(created_at='' OR created_at='0000-00-00 00:00:00') )
and id > 0 ;

You can update this values with some specific time or current timestamp.

To ignore this kind of values you strict the instance using below parameter as @danblack suggested :

set global sql_mode=<< you current value>> + ,NO_ZERO_DATE