Page 1 of 1
order by when using date
Posted: Thu Mar 25, 2004 3:46 am
by irealms
I have a table i need to order by date and then a number at the moment i'm using:
Code: Select all
<?php
$orders = "SELECT orderno,DATE_FORMAT(dateadded, '%d-%m-%Y') AS dateadded,status FROM orders WHERE username='$_SESSION[valid_user]' AND status!='Quote' ORDER BY dateadded DESC,orderno DESC";
?>
but this orders the date by the day only , how do i get it to order by year then month then day?
Posted: Thu Mar 25, 2004 3:54 am
by JayBird
hmmm...wierd, it should order by actually date, not only by the day. Is dateadded an MySQL date field? I presume it is because you are using the DATE_FORMAT function.
Does it order them correctly if you take out "orderno DESC"?
Mark
Posted: Thu Mar 25, 2004 3:56 am
by irealms
nope still orders in same way
Yeah it's a date field too

Posted: Thu Mar 25, 2004 5:13 am
by markl999
Try ...
$orders = "SELECT orderno,DATE_FORMAT(dateadded, '%d-%m-%Y') AS fdateadded,status FROM orders WHERE username='$_SESSION[valid_user]' AND status!='Quote' ORDER BY dateadded DESC,orderno DESC";
and use fdateadded in your display/fetch code. ie instead of doing AS dateadded try doing AS somethingelse ( i used fdateadded), but leave the ORDER BY dateadded as is.
Posted: Fri Mar 26, 2004 2:32 am
by irealms
thats great thanks, worked fine
Strange why you need to do that, i assume it's because you've performed an operation on the field already?