How to fix MySQL incorrect datetime value error

The MySQL Incorrect datetime value error (which is also known as ERROR 1292) is triggered when you perform an INSERT statement that contains one or more DATETIME values with the wrong format.

MySQL only accepts DATETIME values in the format of YYYY-MM-DD hh:mm:ss for string type or YYYYMMDDhhmmss for integer type.

To show you an example, suppose you have a table with DATE and DATETIME columns as shown below:

+-------------+--------------+------+
| Field       | Type         | Null |
+-------------+--------------+------+
| id          | int unsigned | NO   |
| join_date   | date         | YES  |
| last_update | datetime     | YES  |
+-------------+--------------+------+

Now suppose you want to INSERT a new value to the last_update column with the following statement:

INSERT INTO example (last_update) values("10-17-2021 15:40:10");

MySQL will throw an error as shown below:

ERROR 1292 (22007): Incorrect datetime value: '10-17-2021 15:40:10' 
for column 'last_update' at row 1

This is because the DATETIME value in the statement above uses the DD-MM-YYYY HH:MM:SS format, which is unacceptable by MySQL.

The obvious way to fix the error is to change the formatting of your value into the format that MySQL can accept.

But rather than editing the value manually, you can use the STR_TO_DATE() function to help you convert the string value into date value.

Here’s an example of STR_TO_DATE() function in action:

SELECT STR_TO_DATE("10-17-2021 15:40:10", "%m-%d-%Y %H:%i:%s");

-- 2021-10-17 15:40:10  

The STR_TO_DATE() function requires two arguments to run:

• The datetime string that you want to convert
• The format of the datetime string you pass to the function

Because there are many valid datetime formats in the world, it’s impossible for MySQL to guess what format the string value you passed to the function.

The STR_TO_DATE() function uses the same specifiers as the DATE_FORMAT() function that you can see here.

With the STR_TO_DATE() function, the previous INSERT statement won’t cause an error:

INSERT INTO example (last_update) 
  values(STR_TO_DATE("10-17-2021 15:40:10", "%m-%d-%Y %H:%i:%s"));

And that’s how you can fix the error Incorrect datetime value in MySQL.

Keep in mind that the error can also be triggered when you try to insert a DATE value to a DATE type column as follows:

mysql> INSERT INTO example (join_date) values("10-17-2021");

ERROR 1292 (22007): Incorrect date value: '10-17-2021' 
for column 'join_date' at row 1

The error will say Incorrect date value instead of Incorrect datetime value, but they are both the same error.

You can use STR_TO_DATE() to format your date value as shown below:

INSERT INTO example (join_date) 
  values(STR_TO_DATE("10-17-2021", "%m-%d-%Y"));

Just remember that you need to pass the right format to the STR_TO_DATE() function, or MySQL won’t be able to process your value.

In another example, if you’re using the forward slash / as the separator for your date value, then you need to use the same separator for the format parameter:

INSERT INTO example (join_date) 
  values(STR_TO_DATE("10/17/2021", "%m/%d/%Y"));

When you have many values that you want to insert to your table, using STR_TO_DATE function will save you from having to edit your values format manually.

But if you only want to insert a single value, then formatting your value manually might be faster.

Now you’ve learned how to fix the invalid datetime value error in MySQL database server. Great job! 👍