order by when using date

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
User avatar
irealms
Forum Contributor
Posts: 215
Joined: Mon Apr 28, 2003 7:10 am
Location: Leeds

order by when using date

Post 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?
User avatar
JayBird
Admin
Posts: 4524
Joined: Wed Aug 13, 2003 7:02 am
Location: York, UK
Contact:

Post 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
User avatar
irealms
Forum Contributor
Posts: 215
Joined: Mon Apr 28, 2003 7:10 am
Location: Leeds

Post by irealms »

nope still orders in same way :(

Yeah it's a date field too :)
User avatar
markl999
DevNet Resident
Posts: 1972
Joined: Thu Oct 16, 2003 5:49 pm
Location: Manchester (UK)

Post 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.
User avatar
irealms
Forum Contributor
Posts: 215
Joined: Mon Apr 28, 2003 7:10 am
Location: Leeds

Post 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?
Post Reply