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
ORDER BY date ASC
Moderator: General Moderators
Code: Select all
$query="SELECT id, DATE_FORMAT(date, '%d %M %Y') as `date`, event FROM events ORDER BY `date`;- CoderGoblin
- DevNet Resident
- Posts: 1425
- Joined: Tue Mar 16, 2004 10:03 am
- Location: Aachen, Germany
try
When you change the format it is converted to text and sorting as text.
Code: Select all
SELECT id, DATE_FORMAT(date, '%d %M %Y') as use_date, event, date FROM events ORDER BY date DESCThe 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).

The only way to correctly sort by date is to have a timestamp or use the universal date format (yyyy/mm/dd).