本文介绍了插入MySQL时出现DateTime错误的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！
问题描述
将以下数据插入MySQL时出现错误.我该如何解决?
I come up with an error while inserting the following data into MySQL. How can I fix it?
错误1292:第1行"TimeStamp"列的日期时间值不正确:"17/07/2013 18:33:55"
ERROR 1292: Incorrect datetime value: '17/07/2013 18:33:55' for column 'TimeStamp' at row 1
SQL语句:
INSERT INTO `wngtest`.`sitereading` (`idSiteReading`, `TimeStamp`, `SiteLocation`, `Flow`, `Temperature1`, `Temperature2`) VALUES ('1', '17/07/2013 18:33:55', 'WNGSite1', '13.1', '81', '45')
SQL语句:
INSERT INTO `wngtest`.`sitereading` (`idSiteReading`, `TimeStamp`, `SiteLocation`, `Flow`, `Temperature1`, `Temperature2`) VALUES ('2', '17/07/2013 18:18:53', 'WNGSite1', '13', '80', '45')
SQL语句:
INSERT INTO `wngtest`.`sitereading` (`idSiteReading`, `TimeStamp`, `SiteLocation`, `Flow`, `Temperature1`, `Temperature2`) VALUES ('3', '17/07/2013 18:03:54', 'WNGSite1', '12.7', '80', '45')
SQL语句:
INSERT INTO `wngtest`.`sitereading` (`idSiteReading`, `TimeStamp`, `SiteLocation`, `Flow`, `Temperature1`, `Temperature2`) VALUES ('4', '17/07/2013 17:48:54', 'WNGSite1', '12.7', '80', '45')
SQL语句:
INSERT INTO `wngtest`.`sitereading` (`idSiteReading`, `TimeStamp`, `SiteLocation`, `Flow`, `Temperature1`, `Temperature2`) VALUES ('5', '17/07/2013 17:33:55', 'WNGSite1', '12.8', '80', '45')
SQL语句:
INSERT INTO `wngtest`.`sitereading` (`idSiteReading`, `TimeStamp`, `SiteLocation`, `Flow`, `Temperature1`, `Temperature2`) VALUES ('6', '17/07/2013 17:18:55', 'WNGSite1', '12.9', '80', '45')
推荐答案
或者，您可以使用 STR_TO_DATE() :
Alternatively, you can automatically re-format your date string to SQL-99 format using STR_TO_DATE():
STR_TO_DATE( '17/07/2013 18:33:55', '%d/%m/%Y %H:%i:%s')
因此INSERT语句将是:
So the INSERT statement would be:
INSERT INTO wngtest.sitereading
(idSiteReading, TimeStamp, SiteLocation, Flow, Temperature1, Temperature2)
VALUES ('1',
STR_TO_DATE( '17/07/2013 18:33:55', '%d/%m/%Y %H:%i:%s'),
'WNGSite1', '13.1', '81', '45');
这篇关于插入MySQL时出现DateTime错误的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！