在 MySQL 表中插入时如何修复不正确的日期时间值？-mysql教程-PHP中文网

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

WBOY

发布： 2023-08-31 12:53:06

转载

1188人浏览过

在 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)

登录后复制

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

登录后复制

为了避免上述错误，您可以使用STR_TO_DATE()。

语法如下所示

MagicStudio

图片处理必备效率神器！为你的图片提供神奇魔法

102

查看详情

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)

登录后复制

使用select语句显示表中的所有记录。

查询如下所示

mysql> select *from CorrectDatetimeDemo;

登录后复制

以下是输出结果

+----+---------------------+
| 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)

登录后复制

以上就是在 MySQL 表中插入时如何修复不正确的日期时间值？的详细内容，更多请关注php中文网其它相关文章！