Jump to content

Archived

This topic is now archived and is closed to further replies.

marco-marco

ORDER BY....

Recommended Posts

When i use the following code, in order to ORDER BY dateStart, it orders by the day due to using date format.

ie:

18-01-2005

19-03-2004

27-12-2005

 

is there anyway to order by the the year month then day in this date format?

 

DATE_FORMAT(dateStart,\'%d-%m-%Y\' )AS dateStart,

DATE_FORMAT(dateEnd, \'%d-%m-%Y\')AS dateEnd

ORDER BY dateStart 

Share this post


Link to post
Share on other sites

What type is the column you are trying to order by?

Share this post


Link to post
Share on other sites

its set to date field.

 

order by works fine if i dont use date format, but with it, it reverses. so orders by date, month then year, instead of year month date

Share this post


Link to post
Share on other sites

Ok, you can then order by that column without applying any type of format. Formatting is converting the column to a string, and it\'s underlying format is an integer which will intrinsically sort as you would expect. If you want to format the columns for display, that is not a problem and can be done seperately from the order by, which is to say that you can have columns in your order by that are not in your select list.

 

In your example, simply use a different alias for your formatted date, rather than re-aliasing the original column name and all should work as you want it to.

Share this post


Link to post
Share on other sites

×

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.