2

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.

1
  • 1
    given that your output said it changed 6 rows maybe nothing is required.
    – danblack
    Mar 19, 2021 at 2:09

2 Answers 2

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

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.