How to fix incorrect datetime value when inserting into MySQL table?

To avoid the incorrect datetime value error, you can use the STR_TO_DATE() method.

As we know the datetime format is YYYY-MM-DD and if you won't insert in the same format, the error would get generated.

Let us see what actually lead to this error. For this, let us create a new table. The query to create a table is as follows

mysql> create table CorrectDatetimeDemo
   - > (
   - > Id int NOT NULL AUTO_INCREMENT PRIMARY KEY,
   - > ArrivalTime datetime
   - > );
Query OK, 0 rows affected (0.63 sec)

The occurs when we try to include a date with an incorrect datetime format

mysql> insert into CorrectDatetimeDemo(ArrivalTime) values('18/02/2019 11:15:45');
ERROR 1292 (22007): Incorrect datetime value: '18/02/2019 11:15:45' for column 'ArrivalTime' at row 1

To avoid the above error, you can use STR_TO_DATE().

The syntax is as follows

INSERT INTO yourTableName(yourDateTimeColumnName) VALUES (STR_TO_DATE('yourDateTimeValue','%d/%m/%Y %H:%i:%s'));

Now, let us insert the datetime again with the correct format as shown in the above syntax.

The query is as follows

mysql> insert into CorrectDatetimeDemo(ArrivalTime) values(STR_TO_DATE('18/02/2019 11:15:45','%d/%m/%Y %H:%i:%s'));
Query OK, 1 row affected (0.21 sec)

mysql> insert into CorrectDatetimeDemo(ArrivalTime) values(STR_TO_DATE('15/01/2017 10:10:15','%d/%m/%Y %H:%i:%s'));
Query OK, 1 row affected (0.16 sec)

mysql> insert into CorrectDatetimeDemo(ArrivalTime) values(STR_TO_DATE('12/04/2016 15:30:35','%d/%m/%Y %H:%i:%s'));
Query OK, 1 row affected (0.20 sec)

Use the select statement to display all records in the table.

The query is as follows

mysql> select *from CorrectDatetimeDemo;

The following is the output result

+----+---------------------+
| Id | ArrivalTime         |
+----+---------------------+
|  1 | 2019-02-18 11:15:45 |
|  2 | 2017-01-15 10:10:15 |
|  3 | 2016-04-12 15:30:35 |
+----+---------------------+
3 rows in set (0.00 sec)

The above is the detailed content of How to fix incorrect datetime value when inserting into MySQL table?. For more information, please follow other related articles on the PHP Chinese website!