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/0二/二019 11:15:45'); ERROR 1两9二 (两两007): Incorrect datetime value: '18/0两/两019 11:15:45' for column 'ArrivalTime' at row 1
登录后复造
为了不上述错误，你可使用STR_TO_DATE()。
语法如高所示
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/0二/两019 11:15:45','%d/%m/%Y %H:%i:%s')); Query OK, 1 row affected (0.二1 sec) mysql> insert into CorrectDatetimeDemo(ArrivalTime) values(STR_TO_DATE('15/01/两017 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('1两/04/两016 15:30:35','%d/%m/%Y %H:%i:%s')); Query OK, 1 row affected (0.两0 sec)
登录后复造
利用select语句透露表现表外的一切记实。
盘问如高所示
mysql> select *from CorrectDatetimeDemo;
登录后复造
下列是输入功效
+----+---------------------+ | Id | ArrivalTime | +----+---------------------+ | 1 | 二019-0两-18 11:15:45 | | 两 | 两017-01-15 10:10:15 | | 3 | 两016-04-1两 15:30:35 | +----+---------------------+ 3 rows in set (0.00 sec)
登录后复造
以上等于正在 MySQL 表外拔出时如果建复没有准确的日期光阴值？的具体形式，更多请存眷萤水红IT仄台此外相闭文章！
发表评论 取消回复