DateTime error while insert to MySQL

Question

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

Kevin Bowersox · Accepted Answer · 2013-07-25 23:05:58Z

12

In MySql dates should be inserted in yyyy-mm-dd format

Try using the format:

2013-07-17 17:18:55

Full Insert Statement:

INSERT INTO wngtest.sitereading 
(idSiteReading, TimeStamp, SiteLocation, Flow, Temperature1, Temperature2) 
VALUES ('1', '2013-07-17 18:33:55', 'WNGSite1', '13.1', '81', '45');

answered Jul 25, 2013 at 23:05

Kevin Bowersox

94.6k19 gold badges164 silver badges198 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Michael Tsang Over a year ago

but I type '2100-06-04 00:00:00' into a query mysql also errs

fenway · Accepted Answer · 2013-07-25 23:39:40Z

11

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');

answered Jul 25, 2013 at 23:39

fenway

4462 silver badges8 bronze badges

Collectives™ on Stack Overflow

DateTime error while insert to MySQL

2 Answers 2

1 Comment

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related