Ordering Dates

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
icesolid
Forum Regular
Posts: 502
Joined: Mon May 06, 2002 9:36 pm
Location: Buffalo, NY

Ordering Dates

Post by icesolid »

I have a database of information that contains a date_completed field with the type of DATE 0000-00-00.

When I go to print out these fields and I want them to print date_completed DESC. Dates in January 2007 display at the end and dates from December 2006 print at the begining. How do I make the dates from January 2007 print at the top.

Meaning I want to know how to print out the dates according to year, month, then day. I don't want it to put all of the dates together that have a month of 01 together if they are from different years.
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post by feyd »

It's sorting them as you want it.

December 2006 was before January 2007.
icesolid
Forum Regular
Posts: 502
Joined: Mon May 06, 2002 9:36 pm
Location: Buffalo, NY

Post by icesolid »

Here is my code:

Code: Select all

$result = mysql_query("SELECT *,DATE_FORMAT(date_ordered, '%m-%d') AS date_ordered,DATE_FORMAT(date_completed, '%m-%d') AS date_completed FROM cases WHERE user_code='" . $_SESSION["user_code"] . "' AND sent_to_customer='true' ORDER BY date_completed ASC");
When they print out ASC they dates show up with 12 first because its using the months.
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post by feyd »

Your ORDER BY may be confusing the original "date_completed" with your formatted version.
icesolid
Forum Regular
Posts: 502
Joined: Mon May 06, 2002 9:36 pm
Location: Buffalo, NY

Post by icesolid »

Thats what I was thinking but wasent sure. I will try it without the format.
Post Reply