«
»

  • MySQL Tip – Ordering by Date stored in a varchar field

    Databases
    2nd June 2008 -By Vinu Thomas
    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

  • 15 Comments to “MySQL Tip – Ordering by Date stored in a varchar field”

    • sathishkumar on June 14, 2008

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

    • vinu on June 15, 2008

      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’ )

    • MySQL Error: 1062 Duplicate entry ‘0′ for key 1 | VT's Tech Blog on June 18, 2008

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

    • Rodrigo on July 30, 2008

      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

    • Abenzoar UIcab on August 15, 2008

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

    • Alain Garcia on September 3, 2008

      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

    • vinu on September 3, 2008

      Thanks for sharing your tip Alain

    • Mario on May 13, 2009

      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 on May 14, 2009

      Thanks for your tip too Mario

    • yms on October 13, 2009

      Thanks man, you're my new hero!

    • yms on October 13, 2009

      Thanks man, you're my new hero!

    • Mohamed on October 18, 2009

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

    • vinu on October 19, 2009

      @Mohamed Which version exactly do you use ?

    • vinu on October 19, 2009

      @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

    • Getshashy1 on June 10, 2010

      Good post buddy….Really Helpful.

    Leave a Reply