ORDER BY....

[marco-marco]

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 

[gizmola]

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

[marco-marco]

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

[gizmola]

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.

[marco-marco]

ok, thanks for your help.