Page 1 of 1

ORDER BY date ASC

Posted: Thu Sep 29, 2005 3:37 am
by Jim_Bo
Hi,

I have a query like so:

$query="SELECT id, DATE_FORMAT(date, '%d %M %Y') as date, event FROM events ORDER BY date DESC";
$result=mysql_query($query);

But it doesnt list the ORDER BY in a correct manner .. I am getting an order of:

30 November 2005


28 April 2008


25 December 2006


How do I correct this to make it order ASC:


30 November 2005

25 December 2006

28 April 2008


Thanks

Posted: Thu Sep 29, 2005 3:44 am
by ruchit

Code: Select all

$query="SELECT id, DATE_FORMAT(date, '%d %M %Y') as `date`, event FROM events ORDER BY `date`;

Posted: Thu Sep 29, 2005 3:56 am
by Jim_Bo
hi,

it seems to give the same result as the original code

Cheers

Posted: Thu Sep 29, 2005 4:31 am
by CoderGoblin
try

Code: Select all

SELECT id, DATE_FORMAT(date, '%d %M %Y') as use_date, event, date FROM events ORDER BY date DESC
When you change the format it is converted to text and sorting as text.

Posted: Thu Sep 29, 2005 4:50 am
by Jim_Bo
Hi,

That works great


Cheers

Posted: Fri Sep 30, 2005 1:11 pm
by Skara
The reasoning behind this is because it doesn't recognize 'd M Y' as a date. It orders first by the number in front (the day), then by the lettering in the second word (month), then the number at the end (year).
The only way to correctly sort by date is to have a timestamp or use the universal date format (yyyy/mm/dd).

;)