DateTime error when pasting into MySQL

Question

I came up with an error by inserting the following data into MySQL. How can i fix this?

ERROR 1292: Invalid date and time value: '17 / 07/2013 18:33:55 'for the TimeStamp column in 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: Invalid date and time value: '17 / 07/2013 18:18:53 'for the "TimeStamp" column in 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: Invalid date and time value: '17 / 07/2013 18:03:54 'for the "TimeStamp" column in 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: Invalid date and time value: '17 / 07/2013 17:48:54 'for the "TimeStamp" column in 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: Invalid date and time value: '17 / 07/2013 17:33:55 'for the "TimeStamp" column in 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: invalid date and time value: '17 / 07/2013 17:18:55 'for the column "TimeStamp" in 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') 

Answer 1

Alternatively, you can automatically reformat the 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') 

Thus, the INSERT statement will look like this:

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

Answer 2

In MySql, dates must be inserted in the format yyyy-mm-dd

Try using the format:

2013-07-17 17:18:55

Full Insertion Statement:

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