Search This Blog

Sunday, November 18, 2018

MySQL Incorrect datetime value: '0000-00-00 00:00:00'

update user_contact uc set date=now() where'0000-00-00 00:00:00'

won't work.

Change to  CAST( AS CHAR(20)) works.

delete uc from user_contact uc where CAST( AS CHAR(20))='0000-00-00 00:00:00' ;

For replacing a "0000-00-00" date only without timestamp, I used CHAR(11).

It's likely that the sql_mode setting for your session includes NO_ZERO_DATE.

Change the sql_mode to allow zero dates, by removing NO_ZERO_DATE and NO_ZERO_IN_DATE. 

The other reason it is failing is because of the STRICT_TRANS_TABLES mode. As mysql documentation say:

Strict mode affects whether the server permits '0000-00-00' as a valid date: If strict mode is not enabled, '0000-00-00' is permitted and inserts produce no warning. If strict mode is enabled, '0000-00-00' is not permitted and inserts produce an error, unless IGNORE is given as well. For INSERT IGNORE and UPDATE IGNORE, '0000-00-00' is permitted and inserts produce a warning

The same is valid for datetime.