Page 1 of 1

[resolved] Order by Date - Sorting Question

Posted: Thu Dec 22, 2005 2:41 pm
by cdrees
I have a question about ordering by date. If I order my query so that I'm displaying my dates in December 21, 2005 format, how can I sort by date order, and not alphabetically by the name of the month?

If other words, when I do this

Code: Select all

Select * FROM table ORDER BY course_date
I get this:

2005-12-20
2005-12-30
2006-01-15
2006-02-11


When I do this however, my sort gets screwed up:

Code: Select all

SELECT date_format(record_date,'%M %d, %Y') as course_date FROM table order by course_date
December 12, 2005
December 30, 2005
February 11, 2006
January 15, 2006

(February is listed before January because it's ordered alphabetically).


How do I correct that so it's sorting chronologically but giving me the more friendly date formatting? Thanks!!



-Chris

Posted: Thu Dec 22, 2005 2:49 pm
by pickle
Assuming 'record_date' is the column without formatting, this query should work:

Code: Select all

SELECT
   date_format(record_date,'%M %d, %Y') as 'course_date'
FROM
   myTable
ORDER BY
   record_date

worked like a charm

Posted: Thu Dec 22, 2005 10:47 pm
by cdrees
Thanks dude.... worked great