3

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?

Jonathan
  • 3,016
  • 9
  • 43
  • 74
  • Tried reformatting the order by with DATE_FORMAT? This might get the query to handle the order by as a date instead of a string. Haven't tested it though so not 100% sure it will solve it – Sondre Feb 22 '11 at 15:49
  • If table_date is a varchar, the order by will order alphabetically – Xavinou Feb 22 '11 at 15:49

4 Answers4

14

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;
Tomas
  • 5,067
  • 1
  • 35
  • 39
8

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.

paxdiablo
  • 854,327
  • 234
  • 1,573
  • 1,953
6

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.

Frank Heikens
  • 117,544
  • 24
  • 142
  • 135
6

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

Saurabh Gokhale
  • 53,625
  • 36
  • 139
  • 164
  • 1
    This will be very slow on larger tables, an index on the date can't be used. The real problem is the datatype, that's the problem that the OP has to solve. – Frank Heikens Feb 22 '11 at 15:59
  • Create a new column using DATE as datatype, update all records using the old content and str_to_date(), test the result, drop the old column. VARCHAR is useless for dates. – Frank Heikens Feb 22 '11 at 16:02