Results 1 to 6 of 6

Thread: sort by month

  1. #1
    Join Date
    Mar 2007
    Posts
    29

    Unanswered: sort by month

    Hi Friends,

    I have a table month with name of the month as column values. I want to sort them by month. How can I do that in sybase? Please help me.

    create table month(name varchar(10))

    select * from month

    name
    ----------
    Jan
    Feb
    Mar
    May
    Jul
    Apr
    Dec

    I want to sort them by month so that my output should look like..

    name
    ----------
    Jan
    Feb
    Mar
    Apr
    May
    Jul
    Dec

    Thanks in advance,
    Shiya

  2. #2
    Join Date
    Sep 2003
    Location
    Switzerland
    Posts
    443
    Just having Jan,Feb etc and trying to sort wont work because it will do a character sort.

    Suggest having numbers along with it.
    i.e.

    create table month(mth int, mth_str char(3))
    insert into month values(1,'Jan')
    etc
    and then use order by mth.

    Also, not sure what the larger scope of your issue is. There are a lot of date functions which you can use along with maybe a date field. Check sybooks.sybase.com and use the reference manual/functions.

  3. #3
    Join Date
    May 2005
    Location
    South Africa
    Posts
    1,365
    Provided Answers: 1
    order by convert(datetime,'1 '+name+' 2000')

    -- OR --

    order by case name
    when 'Jan' then 1
    when 'Feb' then 2
    when 'Mar' then 3
    -- etc.
    when 'Dec' then 12
    else 13
    end

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Code:
    order by charindex(name,'DecNovOctSepAugJulJunMayAprMarFebJan') desc
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  5. #5
    Join Date
    Sep 2003
    Location
    Switzerland
    Posts
    443
    Quote Originally Posted by r937
    Code:
    order by charindex(name,'DecNovOctSepAugJulJunMayAprMarFebJan') desc
    very nice

  6. #6
    Join Date
    Mar 2007
    Posts
    29
    Thanks a lot for all the replies

    Shiya

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •