Why am I getting MySQL Error Code 1292: Incorrect date value?

MySQL Error Code 1292: Analysis and Resolution

When attempting to insert data into the ALBERGO table, an error message is encountered: "Error Code: 1292. Incorrect date value: '01-05-2012' for column 'data_apertura' at row 1." This error indicates an issue with the date format specified in the query.

Understanding the Error Code

Error code 1292 is specifically related to incorrect date values. The MySQL documentation states that invalid date values such as '0000-00-00 00:00:00' are not permitted in version 5.7 and above.

Troubleshooting and Solution

To resolve this error, the date format must be modified to adhere to the MySQL 5.7 requirements. The problem lies in the date value '01-05-2012' being in the format dd/mm/yyyy rather than the expected yyyy-mm-dd format.

To rectify this, locate the my.cnf file on your system, typically found at /etc/mysql/my.cnf. For Windows users, it is generally located at C:ProgramDataMySQLMySQL Server 8.0my.ini.

Edit the file and navigate to the [mysqld] section. Add the following lines within this section:

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

This modified sql_mode will allow MySQL to accept date values that were previously invalid, including '01-05-2012'.

Save the changes to the my.cnf file and restart the MySQL service using the appropriate commands for your operating system (e.g., sudo service mysql restart for Linux).

Updated Query

With the modified sql_mode, you can now execute the insert query with the corrected date format:

INSERT INTO `PROGETTO`.`ALBERGO`
(`ID`, `nome`, `viale`, `num_civico`, `data_apertura`, `data_chiusura`, `orario_apertura`, `orario_chiusura`, `posti_liberi`, `costo_intero`, `costo_ridotto`, `stelle`, `telefono`, `mail`, `web`, `Nome-paese`, `Comune`)
VALUES
(0, 'Hotel Centrale', 'Via Passo Rolle', '74', '2012-05-01', '2012-09-31', '06:30', '24:00', 80, 50, 25, 3, '43968083', '[email protected]', 'http://www.hcentrale.it/', 'Trento', 'TN');

This modified query will now execute successfully without the error code 1292.

The above is the detailed content of Why am I getting MySQL Error Code 1292: Incorrect date value?. For more information, please follow other related articles on the PHP Chinese website!