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()。
语法如下所示
ViiTor实时翻译
AI实时多语言翻译专家！强大的语音识别、AR翻译功能。
116 查看详情
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 表中插入时如何修复不正确的日期时间值？的详细内容，更多请关注创想鸟其它相关文章！
版权声明：本文内容由互联网用户自发贡献，该文观点仅代表作者本人。本站仅提供信息存储空间服务，不拥有所有权，不承担相关法律责任。
如发现本站有涉嫌抄袭侵权/违法违规的内容， 请发送邮件至 chuangxiangniao@163.com 举报，一经查实，本站将立刻删除。
发布者：程序猿，转转请注明出处：https://www.chuangxiangniao.com/p/324759.html
微信扫一扫 
支付宝扫一扫 
