Date sorting problem in mysql query

[bluefrog]

Hi I'm trying to sort a list of domain names by expiry date, BUT, I have the date format of %M %e, %Y which is sorting by the alphabetised month names. Here is the query:

$data = mysql_query
("SELECT domain,description, date_format(exp_date, '%M %e, %Y') as exp_date 
FROM domain1_domains 
WHERE exp_date >'" . date("Y-m-d") . "'ORDER BY exp_date ASC LIMIT 0, 10")  or die(mysql_error());

 

Anybody got a simple workaround or is my query just wrong? (more than a possibility!!) lol

 

This is obviously all on one line as a single query, just split for display purposes.

 

Thanks in advance to any kind soul who will give up a part of their day to help

[bluefrog]

Never mind I think I got it:

 

Changed

 

ORDER BY exp_dateASC LIMIT 0, 10

 

to ORDER BY date_format(exp_date, '%Y, %m, %d') ASC LIMIT 0, 10

[PFMaBiSmAd]

Your exp_date column can directly be used in the ORDER BY since it is a DATE data type. However, when you used exp_date as the alias name for the date_format() term, the date_format() result is what is used in the ORDER BY.

 

By putting in an additional date_format() in the ORDER BY, you are just forcing your column value to be used instead of the alias of the first date_format(). This adds extra processing.

 

The best solution would be to use a different alias name and then just use your column name in the ORDER BY.