How to fix incorrect datetime value when inserting into MySQL table?-Mysql Tutorial-php.cn

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

WBOY

Release： 2023-08-31 12:53:06

forward

1017 people have browsed it

在 MySQL 表中插入时如何修复不正确的日期时间值？

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)

Copy after login

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

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

Copy after login

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

The syntax is as follows

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

Copy after login

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(&#39;18/02/2019 11:15:45&#39;,&#39;%d/%m/%Y %H:%i:%s&#39;));
Query OK, 1 row affected (0.21 sec)

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

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

Copy after login

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

The query is as follows

mysql> select *from CorrectDatetimeDemo;

Copy after login

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)

Copy after login

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!