timw Posted December 19, 2012 Share Posted December 19, 2012 Hi, My current mysql db has a varchar field with a date stored in the following format 'Aug 12, 2011' or 'Sept 9, 2012' etc. Do any of you know of a good way I could select the dates and insert in to a new date field in the correct YYYY-mm-dd format? Many thanks, Tim Quote Link to comment Share on other sites More sharing options...
requinix Posted December 19, 2012 Share Posted December 19, 2012 STR_TO_DATE would be a great place to start looking. Actually that's the only thing you'll need. Quote Link to comment Share on other sites More sharing options...
timw Posted December 19, 2012 Author Share Posted December 19, 2012 Yep worked a treat had to do a bit of tidying first as i had a few April's not in as Apr and Sept's not in as Sep UPDATE table SET var_field = REPLACE(table.var_field,'April','Apr') WHERE table.var_field LIKE '%April%'; Then ran UPDATE table SET date_field = STR_TO_DATE(var_field, '%b %e, %Y') WHERE id = id; Thanks, Tim 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.