Jump to content


Photo

ORDER BY....


  • Please log in to reply
4 replies to this topic

#1 marco-marco

marco-marco
  • Members
  • Pip
  • Newbie
  • 9 posts

Posted 30 December 2003 - 08:53 PM

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 dateEndORDER BY dateStart


#2 gizmola

gizmola
  • Administrators
  • Advanced Member
  • 4,664 posts
  • LocationLos Angeles, CA USA

Posted 30 December 2003 - 11:02 PM

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

#3 marco-marco

marco-marco
  • Members
  • Pip
  • Newbie
  • 9 posts

Posted 30 December 2003 - 11:20 PM

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

#4 gizmola

gizmola
  • Administrators
  • Advanced Member
  • 4,664 posts
  • LocationLos Angeles, CA USA

Posted 30 December 2003 - 11:27 PM

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.

#5 marco-marco

marco-marco
  • Members
  • Pip
  • Newbie
  • 9 posts

Posted 30 December 2003 - 11:31 PM

ok, thanks for your help.




0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users