Order by date (varchar)?

Question

I want to order by date.

e.g.

table_date
February 2011
January 2011
December 2010

I've already tried:

SELECT distinct(table_date) FROM tables ORDER BY table_date DESC

bur it doesn't work.

I get this instead:

January 2011
February 2011
December 2010

Can you help me please?

Answer 1

Store dates as DATE, not as VARCHAR, that's a huge mistake. Use STR_TO_DATE() to convert your content. When you're done, you can order by dates without any problems.

Answer 2

If you want to order by date, store it as a date, not a string. Unless your date string is of the form yyyy-mm-dd, it will not sort as you want it.

Databases are hard enough work as-is, without people making it harder, and you should be striving as much as possible to avoid what I like to call SQL gymnastics.

Store it as a date then, if you must, use date functions to get it in the form February 2011.

It'll be a lot easier going that way than what you're trying to do.

---

Even if you can't change any of the current columns due to code restrictions, you can always add another column to the database like TABLE_DATE_AS_DATE and put in an insert/update trigger to populate it based on TABLE-DATE.

Then just do:

update table x set table_date = table_date

or something similar, to fire the trigger for all rows.

Then, your query can still get at table_date but use table_date_as_date for ordering. That's a kludge of course but I've had to use tricks like that in the past when it was imperative the code could not change, so we had to resort to DBMS trickery.

Answer 3

Date should be stored as date and not VARCHAR.

Suppose you have table_date in the following format (DD-MM-YYYY)

table_date  
2011-01-01 
2011-02-01   
2010-12-01  

Now you can perform order by clause in the following way

SELECT * FROM table_order ORDER BY str_to_date(date, "%Y-%M-%D") ASC  

I doubt if the output will be in ordered form

Answer 4

If you must store the dates in a varchar which as others pointed out is not recommended, you could use:

SELECT table_date FROM tables ORDER BY STR_TO_DATE(table_date, '%M %Y') DESC;