MySQL Tip – Ordering by Date stored in a varchar field

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&gt; 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&gt; 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

yms

Thanks man, you're my new hero!

vinuthomas

Thanks for your tip too Mario

Mario

Thanks for sharing.
Just a note, you can also sort in this way by "sort by"

Example
SELECT * FROM `TABLE` ORDER BY STR_TO_DATE(date, '%d.%m.%Y') DESC
Regards

vinuthomas

Thanks for sharing your tip Alain

Alain Garcia

Thanks man!

I have a table (eventos) with 3 columns for store date:

one column for dia (day)
one column for mes (mont)
one column for ano (year)

I use this line:

SELECT * FROM (SELECT *, STR_TO_DATE( CONCAT(ano, '-', mes, '-', dia), '%Y-%m-%d' ) AS fecha FROM eventos) AS resultado

I use this line to:

1. CONCAT the three columns in 1 column
2. convert this new column to date format

Second example

I use this line:

SELECT * FROM (SELECT *, STR_TO_DATE( CONCAT(ano, '-', mes, '-', dia), '%Y-%m-%d' ) AS fecha FROM eventos) AS resultado WHERE fecha >= "2005-5-13" AND fecha = "$date_1" AND fecha <= "$date_2" ORDER BY fecha ASC

VT’s Tech Blog

My Tech Discovery Blog on PHP, Ajax, Security and Web 2.0

MySQL Tip – Ordering by Date stored in a varchar field