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?