Did you ever save dates as a string when you were a n00b? OK I’ll admit it, I did! Want to convert them to
DATETIME fields now? Read on!
We’ve All Done It!
I’m sure we’ve all used the wrong data type when we were not quite so experienced as today. If you’ve ever saved a date or datetime object as a string rather than its correct MySQL data type then now is the time to fix it! Using the correct data type has many benefits, not least being able to add and subtract time, compare dates, select the date in any format you choose… the list goes on.
MySQL To The Rescue!
MySQL has a great little function called
STR_TO_DATE() that is the inverse of the
DATE_FORMAT() function. It takes a string and a format string, and returns a
So now there’s no excuse for having date strings laying around in your application.
SELECT STR_TO_DATE('01,5,2013','%d,%m,%Y'); -> '2013-05-01'
Update Your Data
STR_TO_DATE() to update your data and then change the column type to
UPDATE mytable SET mydate = STR_TO_DATE(mydate,'%d,%m,%Y'); ALTER TABLE mytable MODIFY mydate DATE;