benanamen Posted December 18, 2015 Share Posted December 18, 2015 (edited) On another forum I ran across someone with a problem I have never had to deal with and am interested in the solution for it. He has dates in varchar formatted as 12/25/2015 and wants to update so the date is formatted as a proper date (YYYY-MM-DD) with a date column type. Off the top of my head I was thinking, Create a new date type column Do an update on the dates to replace the slashes with dashes. UPDATE table SET baddate = REPLACE(baddate, '/', '-'); Then make a little script to query the bad dates in reverse twice and re-insert the data with a foreach to the correct date column. SELECT reverse(baddate) AS rev, (SELECT REVERSE(rev) FROM mytable) FROM mytable foreach ($result AS $row){ //Insert data to real date column } I am wondering what a 100% sql solution would be or any other ideas to deal with it. Edited December 18, 2015 by benanamen Quote Link to comment Share on other sites More sharing options...
mac_gyver Posted December 18, 2015 Share Posted December 18, 2015 STR_TO_DATE() would be used in a single UPDATE query to take any formatted date and produce a mysql date type. Quote Link to comment Share on other sites More sharing options...
benanamen Posted December 18, 2015 Author Share Posted December 18, 2015 After some digging I found something that works UPDATE your_table SET date_field = DATE(STR_TO_DATE(date_field, '%m/%d/%Y')) WHERE DATE(STR_TO_DATE(date_field, '%m/%d/%Y')) <> '0000-00-00'; Quote Link to comment Share on other sites More sharing options...
Jacques1 Posted December 18, 2015 Share Posted December 18, 2015 (edited) I definitely would not try to fix this with some SQL hack job. A VARCHAR can contain absolutely everything, and nobody knows if the dates have been validated properly, so simply assuming that the original data is alright seems overly optimistic. I'd do this with a script which actually parses the original dates, validates them rigorously and then either inserts the reformatted date into the database or reports the input for manual correction. If it turns out that all dates could be reformatted automatically, that's great. But I wouldn't rely on it. Also note that the reliability of STR_TO_DATE() is dependend on the MySQL configuration. In less strict SQL modes, nonsense dates like “2015-02-31” are considered valid and will be accepted with no warning whatsoever. Edited December 18, 2015 by Jacques1 Quote Link to comment Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.