Why Am I Getting Error Code 1292: Incorrect Date Value in MySQL and How Can I Fix It?

Error Code 1292: Incorrect Date Value in MySQL

When working with MySQL, you may encounter the error code 1292, indicating an incorrect date value. This error typically occurs when attempting to insert a date value that does not conform to the expected format or constraints.

Question:

I'm trying to insert a date value ('01-05-2012') into the data_apertura column of the ALBERGO table, but I'm getting the error code 1292. I've tried changing the date format from 'dd/mm/yyyy' to 'dd-mm-yyyy', but it doesn't seem to resolve the issue. What changes should I make?

Answer:

The error in this case is not related to the date format, but rather a setting in MySQL 5.7 that disallows certain date values, specifically those formatted as '0000-00-00 00:00:00'. To allow such values, you need to update your MySQL configuration file (my.cnf) as follows:

1. Open the configuration file using a text editor:

sudo nano /etc/mysql/my.cnf

2. Locate the [mysqld] section and add the following line after it:

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

3. Save the file and restart the MySQL service:

sudo service mysql restart

With these changes, MySQL will now allow date values such as '01-05-2012' to be inserted into the data_apertura column.

The above is the detailed content of Why Am I Getting Error Code 1292: Incorrect Date Value in MySQL and How Can I Fix It?. For more information, please follow other related articles on the PHP Chinese website!