Search code examples
mysqlinnodb

Incorrect date value while creating Date field in InnoDB table MySQL database

I need to create a date field in InnoDB table but I get this error

ALTER TABLE `tbl_name`  ADD `bdate` DATE NOT NULL  AFTER `another_field`;
MySQL said: Documentation
#1292 - Incorrect date value: '0000-00-00' for column 'bdate' at row 1
Solution

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

    You need to disable date strict mode or need to remove not null from bdate column.

    Why it's happening?

    You'r saying bdate is not null, it means all old records will have default date populated, since in your case strict date mode is enabled, it means your bdate values which default to 0000-00-00 will become invalid and server will throw the error, which you are seeing.

    Though fixing database side may not be good Idea, its always better to do in App layer if possible.