order by date

[rsoderstrom]

I have a query that selects a few fields then date_formats one of these fields and orders the output by the formatted date: $query2=mysql_query("SELECT reqrmnt,DATE_FORMAT(datedue,'%m-%d-%Y')AS datedue,DATE_FORMAT(datecompleted,'%m-%d-%Y')AS datecompleted,DATE_FORMAT(expires,'%m-%d-%Y')AS expires FROM $usertable WHERE ID='$id' ORDER BY datedue ASC")or die ("Error: ".mysql_error());

The problem is that the order by only uses the m(or month) to order it by when I need to have it ordered by the whole date, m-d-Y. What do I need to do so it recognizes all of the date not just the month.
Thanks for your help.

[Frag]

hmm.. I've never done it in that order before. Why don't you order by date first then format the date second?

It appears to be that your ordering by date after you have converted it to an ascii string. That would be why it orders by only the month.

Hope this helps.

Frag

[KoshNaranek]

for correct sorting i would do something like this...

SELECT..... DATE_FORMAT(datedue,'%Y%m%d') AS sortorder ..... ORDER BY sortorder ASC

12-24-2002 -> 20021224
12-31-2002 -> 20021231
03-18-2003 -> 20030318

"Reverse"-German-Date-Format ... try it 😉