Why am I getting an \'Incorrect Date Value\' Error in MySQL?

Incorrect Date Value Error in MySQL

When attempting to insert data into an MySQL table, an error code 1292 may occur, indicating an incorrect date value. This error is commonly encountered when using a date format that is not supported by MySQL.

In the given example, the query attempts to insert a date in the 'dd-mm-yyyy' format, which is not recognized by MySQL. To resolve this issue, the date format in the query must be changed to a supported format.

With MySQL version 5.7 and above, the default SQL mode is strict, which disallows certain operations, including inserting invalid dates. To allow dates with zero values (such as '0000-00-00 00:00:00'), the SQL mode must be modified.

Steps to Resolve Error 1292:

1. Edit MySQL Configuration File:

   • Open the MySQL configuration file /etc/mysql/my.cnf with a text editor (e.g., nano).
   • Locate the [mysqld] section.

2. Add New SQL Mode Settings:

   • Add the following lines to the [mysqld] section:

   sql_mode="NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION"

3. Restart MySQL Server:

   • Run the following command to restart the MySQL service:

   sudo service mysql restart

4. Re-insert Data:

   • After restarting MySQL, re-execute the query with the correct date format (e.g., '2012-05-01').

5. Additional Notes:

   • For MySQL versions prior to 5.7, the ZERODATE and NO_ZERO_IN_DATE SQL mode settings can be used instead.
   • If the error persists, check the data type and constraints defined for the data_apertura column in the table.

The above is the detailed content of Why am I getting an \'Incorrect Date Value\' Error in MySQL?. For more information, please follow other related articles on the PHP Chinese website!