ordering by text date in MySQL

teknodude

Here's my problem:

In my MySQL DB I have a table with a varchar column that stores dates in the format of "month year" i.e. "January 2002".

The problem is that when I try to ORDER BY in the SQL by the date column it sorts it alphabetically. This is obviously not what I want, but no fiddling around with the DATE_FORMAT function or anything like that has yielded success for me yet 🙁

Here's an example list of dates and the manner in which they should be ordered:

January 2001
July 2001
August 2001
March 2002
April 2002
August 2002

Any help greatly appreciated 🙂 TIA.

teknodude

thom

The following should work:

SELECT *
FROM table
ORDER BY
SUBSTRING_INDEX(stupid_date_column,' ',-1),
FIND_IN_SET(SUBSTRING_INDEX(stupid_date_column,' ',1),'January,February,March,April,May,June,July,August,September,October,November,December')

But! note that storing a date like you did is not very intelligent. A better way would be either to store it in a varchar column in the following format: '200209' (for September 2002) which would make this ordering business a lot easier, or just put this into two columns, which could both just be numeric columns (yeah, and of course you could just use the YEAR column type. Good luck 🙂

regards,
thom

killerbishop

I would reimplement the table so that it uses a DATE, DATETIME, or TIMESTAMP field instead of a VARCHAR field. Of course, you would need to export your data, convert the text fields to a suitable form, and import into the new table. Any programs that use this table would have to change accordingly. A reference about time fields in MySQL can be found here:

http://www.mysql.com/doc/en/DATETIME.html

mickfitz

It may not be the best solution but it works guys:

ORDER BY month ('January,February,March,April,May,June,July,August,September,October,November,December')

Mick