MySQL Tip – Ordering by Date stored in a varchar field

MySQL Enterprise Server

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

13 Responses to “MySQL Tip – Ordering by Date stored in a varchar field”
  1. sathishkumar 14 June 2008 at 5:42 pm #

    can u help me the convert varchar to date in mysql and also compare the date in between to and date

  2. vinu 15 June 2008 at 5:03 pm #

    Satish,
    You can use the following query

    SELECT rows
    FROM `table`
    WHERE ‘2006-12-13′
    BETWEEN STR_TO_DATE( vardatefield1, ‘%Y-%m-%d’ )
    AND STR_TO_DATE( vardatefield2, ‘%Y-%m-%d’ )

  3. MySQL Error: 1062 Duplicate entry ‘0′ for key 1 | VT's Tech Blog

    [...] MySQL Tip – Ordering by Date stored in a varchar field [...]

  4. Rodrigo 30 July 2008 at 7:06 pm #

    hi, it is not the more elegant solution, but for small tables and when the client use text to save a date, it is useful.

    Thanks

  5. Abenzoar UIcab 15 August 2008 at 12:00 am #

    Thank’s man…
    It hepl me for a little problem.
    Regards…

  6. Alain Garcia 3 September 2008 at 12:37 am #

    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

  7. vinu 3 September 2008 at 11:13 am #

    Thanks for sharing your tip Alain

  8. Mario 13 May 2009 at 5:15 pm #

    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

  9. vinuthomas 14 May 2009 at 1:00 am #

    Thanks for your tip too Mario

  10. yms 13 October 2009 at 1:07 am #

    Thanks man, you're my new hero!

  11. Mohamed 18 October 2009 at 4:01 pm #

    i use version earllier than mysql 4.1 how can i do that

  12. vinu 19 October 2009 at 12:23 pm #

    @Mohamed Which version exactly do you use ?

  13. vinu 19 October 2009 at 12:24 pm #

    @Mohamed Which version exactly do you use ? STR_TO_DATE should work on 3.23/4.0/4.1 according to the manual – http://dev.mysql.com/doc/refman/4.1/en/date-and-time-functions.html#function_str-to-date

Leave a Reply