Convert string format to date format in MySQL

I was lazy to do conversion from a source to MySQL database record, thus it is in original format, stored as string. E.g : Aug 23 2010 07:00PM

Later, after a long while, I was requested to sort the query output from MySQL database, sort by the date! It is too late to do conversion, and repopulate the table, so... I found this : str_to_date.

str_to_date(`date_str`, '%b %d %Y %h:%i%p')

Example usage :

SELECT str_to_date ('Aug 23 2010 07:00PM', '%b %d %Y %h:%i%p') as date

It will return you this :

2010-08-23 19:00:00

Then you can sort the date accordingly. :)

ORDER BY str_to_date(`date_str`, '%b %d %Y %h:%i%p')

For the specifier, you can refer to http://dev.mysql.com/doc/refman/5.0/en/date-and-time-functions.html#function_date-format