[resolved] Order by Date - Sorting Question

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
cdrees
Forum Newbie
Posts: 7
Joined: Wed Dec 21, 2005 9:26 am

[resolved] Order by Date - Sorting Question

Post 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
Last edited by cdrees on Thu Dec 22, 2005 10:47 pm, edited 1 time in total.
User avatar
pickle
Briney Mod
Posts: 6445
Joined: Mon Jan 19, 2004 6:11 pm
Location: 53.01N x 112.48W
Contact:

Post 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
Real programmers don't comment their code. If it was hard to write, it should be hard to understand.
cdrees
Forum Newbie
Posts: 7
Joined: Wed Dec 21, 2005 9:26 am

worked like a charm

Post by cdrees »

Thanks dude.... worked great
Post Reply