DATE_FORMAT() ORDER BY

[squidster]

Hi,

I have been having problems with UK and MySQL date formats and have been able to resolve most of them so that dates inserted and queried are all ok. However, I have just noticed something strange when attempting to select dates using ORDER BY date ASC/DESC:-

SELECT Id, Id2, Title, Type, Desc, DATE_FORMAT(Date, '%d/%m/%Y') AS Date FROM table WHERE Date >= CURRENT_DATE() ORDER BY Date ASC

This produces a list with dates in UK format but they are not sorted in correct order. It seems to sort left to right by day/month/year.

If I take out the DATE_FORMAT() and leave ORDER BY date ASC then it works fine with the MySQL date format as it sorts in same order but year/month/day.

Does anyone know how/if this can be done with the select, or will I have to reformat the date in the app?

thanks

 

[swampBoogie]

You are ordering by the formatted date value.

SELECT Id, Id2, Title, Type, Desc, DATE_FORMAT(Date, '%d/%m/%Y') AS fDate FROM table WHERE Date >= CURRENT_DATE() ORDER BY Date ASC

 

[squidster]

I can be incredibly dumb sometimes it scares me!!



thanks