How do you sort a varchar field which has the dates stored in it? If you try the usual order by on the varchar fields you’ll have trouble with the sort order, since MySQL will order this field like a varchar and you will surely have problems with dates being sorted this way. Take the following example of a table which has a field called feed_date which defined as varchar. Now ordering the results by feed_date will not give the correct order like in the following example:

mysql> select * from datetest order by feed_date;
+----+-------------------+------------+
| id | feed_name         | feed_date  |
+----+-------------------+------------+
|  1 | VTs Tech Blog     | 1-23-2008  |
|  2 | vinuthomas.com    | 12-23-2006 |
|  3 | sitesandsounds.in | 3-12-2008  |
+----+-------------------+------------+
3 rows in set (0.00 sec)

Hmmm… 2006 coming in between 2008 doesn’t seem right does it? The only way to get MySQL to sort this result properly will be if we can get MySQL to understand this data as a date field. To to this, we can use the mysql function STR_TO_DATE. This function allows us to convert a string to date and allow us to specify in which format the date is present.

So to convert the current date format in the feed_date column which is in the mm-dd-yyyy format, we’ll have to use this syntax: STR_TO_DATE(feed_date, ‘%m-%d-%Y’). Now we can use this converted data to sort on:

mysql> SELECT id, feed_name, feed_date ,
 STR_TO_DATE( feed_date, '%m-%d-%Y' ) AS date_for_sort
 FROM `datetest` ORDER BY date_for_sort;
+----+-------------------+-------------+--------------+
| id   | feed_name        | feed_date  | date_for_sort|
+----+--------------------+------------+--------------+
|  2   | vinuthomas.com   | 12-23-2006 | 2006-12-23   |
|  1   | VTs Tech Blog    | 1-23-2008  | 2008-01-23   |
|  3   | sitesandsounds.in| 3-12-2008  | 2008-03-12   |
+----+--------------------+------------+--------------+
3 rows in set (0.00 sec)

Disclaimer: I know that doing this is going a round about way to getting this done, this is just a tip to help out when we have a problem like this and can’t really change the database field declaration and you have tons of data in the field which doesn’t confirm to the MySQL date format in a varchar field.

Link: MySQL Manual on STR_TO_DATE