Inserting temporal value in MySQL 5.7 or With the NO_ZERO_DATE SQL Mode Enabled Fails: "ERROR 1292 (22007): Incorrect datetime value: '0000-00-00 00:00:00' for column 'val' at row 1"

(Doc ID 2320951.1)

Last updated on OCTOBER 24, 2017

Applies to:

MySQL Server - Version 5.5 and later
Information in this document applies to any platform.

Symptoms

When trying to insert a row - for example when restoring a mysqldump backup - into MySQL 5.7 or with the NO_ZERO_DATE SQL mode enabled, the following error occurs:

The data type mentioned in the error message depends on the data type of the column:

Changes

The issue may for example start after upgrading to MySQL 5.7 or enabling the NO_ZERO_DATE SQL mode (enabled by default in MySQL 5.7 and later).

Cause

Sign In with your My Oracle Support account

Don't have a My Oracle Support account? Click to get started

My Oracle Support provides customers with access to over a
Million Knowledge Articles and hundreds of Community platforms