I have a database with a table 'testimport'. The table contains - a column (DateOccurred) which is in datetime format and contains 0000-00-00 00:00:00 - a column (Comments) which contains 20/03/2013 14:17 in varchar(255) utf8_general_ci format.
I am trying to transfer the content of 'Comments' to 'Dateoccurred' where it should be stored as a date/time, but only for rows with the ID of 2.
I tried this (and various variations) with no luck.
UPDATE testimport SET DateOccurred = DATE_FORMAT(STR_TO_DATE(Comments, '%d/%m/%Y %h:%m:%s'), '%Y-%m-%d %h:%m:%s') WHERE ID=2
This results in the 'DateOccurred' column being changed to a content of 'Null'.
Can anyone advise where I am going wrong.
Change
STR_TO_DATE(Comments, '%d/%m/%Y %h:%m:%s')
to
STR_TO_DATE(Comments, '%d/%m/%Y %H:%i')
as you don't have a seconds marked. Also, minutes use %i and hours need %H.
Check complete list here: MySQL Docs
Your complete query can be shortened/modified to
UPDATE testimport
SET DateOccurred = STR_TO_DATE(Comments, '%d/%m/%Y %H:%i')
WHERE ID = 2