Menu
I come up with an error while inserting the following data into MySQL. How can I fix it?
ERROR 1292: Incorrect datetime value: '17/07/2013 18:33:55' for column 'TimeStamp' at row 1
SQL Statement:
INSERT INTO `wngtest`.`sitereading` (`idSiteReading`, `TimeStamp`, `SiteLocation`, `Flow`, `Temperature1`, `Temperature2`) VALUES ('1', '17/07/2013 18:33:55', 'WNGSite1', '13.1', '81', '45')
ERROR 1292: Incorrect datetime value: '17/07/2013 18:18:53' for column 'TimeStamp' at row 1
SQL Statement:
INSERT INTO `wngtest`.`sitereading` (`idSiteReading`, `TimeStamp`, `SiteLocation`, `Flow`, `Temperature1`, `Temperature2`) VALUES ('2', '17/07/2013 18:18:53', 'WNGSite1', '13', '80', '45')
ERROR 1292: Incorrect datetime value: '17/07/2013 18:03:54' for column 'TimeStamp' at row 1
SQL Statement:
INSERT INTO `wngtest`.`sitereading` (`idSiteReading`, `TimeStamp`, `SiteLocation`, `Flow`, `Temperature1`, `Temperature2`) VALUES ('3', '17/07/2013 18:03:54', 'WNGSite1', '12.7', '80', '45')
ERROR 1292: Incorrect datetime value: '17/07/2013 17:48:54' for column 'TimeStamp' at row 1
SQL Statement:
INSERT INTO `wngtest`.`sitereading` (`idSiteReading`, `TimeStamp`, `SiteLocation`, `Flow`, `Temperature1`, `Temperature2`) VALUES ('4', '17/07/2013 17:48:54', 'WNGSite1', '12.7', '80', '45')
ERROR 1292: Incorrect datetime value: '17/07/2013 17:33:55' for column 'TimeStamp' at row 1
SQL Statement:
INSERT INTO `wngtest`.`sitereading` (`idSiteReading`, `TimeStamp`, `SiteLocation`, `Flow`, `Temperature1`, `Temperature2`) VALUES ('5', '17/07/2013 17:33:55', 'WNGSite1', '12.8', '80', '45')
ERROR 1292: Incorrect datetime value: '17/07/2013 17:18:55' for column 'TimeStamp' at row 1
SQL Statement:
INSERT INTO `wngtest`.`sitereading` (`idSiteReading`, `TimeStamp`, `SiteLocation`, `Flow`, `Temperature1`, `Temperature2`) VALUES ('6', '17/07/2013 17:18:55', 'WNGSite1', '12.9', '80', '45')
835
The obvious way to fix the error is to change the formatting of your value into the format that MySQL can accept. But rather than editing the value manually, you can use the STR_TO_DATE() function to help you convert the string value into date value.
MySQL comes with the following data types for storing a date or a date/time value in the database: DATE - format YYYY-MM-DD. DATETIME - format: YYYY-MM-DD HH:MI:SS. TIMESTAMP - format: YYYY-MM-DD HH:MI:SS.
MySQL retrieves and displays DATE values in ' YYYY-MM-DD ' format. The supported range is '1000-01-01' to '9999-12-31' . The DATETIME type is used for values that contain both date and time parts. MySQL retrieves and displays DATETIME values in ' YYYY-MM-DD hh:mm:ss ' format.
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')
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');
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
