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> 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
can u help me the convert varchar to date in mysql and also compare the date in between to and date
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’ )
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
Thank’s man…
It hepl me for a little problem.
Regards…
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
Thanks for sharing your tip Alain
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
Thanks for your tip too Mario
Thanks man, you're my new hero!
Thanks man, you're my new hero!
i use version earllier than mysql 4.1 how can i do that
@Mohamed Which version exactly do you use ?
@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
Good post buddy….Really Helpful.