marco-marco Posted December 30, 2003 Share Posted December 30, 2003 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 Quote Link to comment Share on other sites More sharing options...
gizmola Posted December 30, 2003 Share Posted December 30, 2003 What type is the column you are trying to order by? Quote Link to comment Share on other sites More sharing options...
marco-marco Posted December 30, 2003 Author Share Posted December 30, 2003 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 Quote Link to comment Share on other sites More sharing options...
gizmola Posted December 30, 2003 Share Posted December 30, 2003 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. Quote Link to comment Share on other sites More sharing options...
marco-marco Posted December 30, 2003 Author Share Posted December 30, 2003 ok, thanks for your help. Quote Link to comment Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.