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 DATE or 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 DATETIME value.
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
Use STR_TO_DATE() to update your data and then change the column type to DATE or DATETIME.
UPDATE mytable SET mydate = STR_TO_DATE(mydate,'%d,%m,%Y'); ALTER TABLE mytable MODIFY mydate DATE; |