How do I fix: Invalid default value for date column (0000-00-00)

Migration errors

Fix: Invalid default value for date column (0000-00-00)

ERROR 1067 (42000): Invalid default value for 'created_at'

Importing an old MySQL dump into MySQL 5.7+/8.x, or converting it to PostgreSQL.

Legacy MySQL allowed zero dates ('0000-00-00'). Modern sql_mode includes NO_ZERO_DATE, and PostgreSQL rejects zero dates entirely.

1. Import-time workaround on MySQL

mysql --init-command="SET sql_mode=''" mydb < dump.sql

2. Clean the data properly

UPDATE t SET created_at = NULL
WHERE created_at = '0000-00-00 00:00:00';

Make the column NULLable first if needed.

3. Converting to PostgreSQL?

Our converter turns zero dates into NULL automatically and counts the affected rows in the report.

Migrating between MySQL and PostgreSQL?

Our converter handles this and dozens of other gotchas automatically — upload a dump, download working SQL.