• Status: Solved
• Priority: Medium
• Security: Public
• Views: 278

# How to sort this kind of data containing month names

Table - mstm1
contains fields month and year
year may contain 5 months or 2 months for any year ie. varied no, of months.

If year 2009 has 3 distinct months as April June September
The output should come as All Months
April
June
September

Similarly if year 2010 has all months
then the output should be
All Months
January
February
March
and so on
0
searchsanjaysharma
• 2
• 2
• 2
• +1
1 Solution

Fixer of ProblemsCommented:
Date and Time columns carry the information as numbers so they will sort properly and you can do date-time arithmetic on the data.  I suggest that you add a column that contains the month as a number or even the year-month like '201001' for January 2010.  Then you can ORDER BY the year and month number.  If you return the text version also, you can use it for display.
0

Author Commented:
No i have two fields seprately, year in bigint and month as monthname not month number.
0

Fixer of ProblemsCommented:
I understand that.  You can't sort 'monthname' properly so I suggest adding a month number column.
0

Commented:
Two approaches I can think of
1.

select *
from yourtable
order by DATEPART(mm, CAST(MonthField + ' 1, ' + cast(YearField as varchar(4)) AS DATETIME))
0

Commented:
2.

select *
from yourtable
order by
YearField,
CASE MonthField
WHEN 'January' THEN 1
WHEN 'February' THEN 2
WHEN 'March' THEN 3
WHEN 'April' THEN 4
WHEN 'May' THEN 5
WHEN 'June' THEN 6
WHEN 'July' THEN 7
WHEN 'August' THEN 8
WHEN 'September' THEN 9
WHEN 'October' THEN 10
WHEN 'November' THEN 11
WHEN 'December' THEN 12
END
0

Senior DBACommented:
ORDER BY
CHARINDEX (MonthField, 'JanuaryFebruaryMarchAprilMayJuneJulyAugustSeptemberOctoberNovemberDecember')
0

Author Commented:
Thanx
0

## Featured Post

• 2
• 2
• 2
• +1
Tackle projects and never again get stuck behind a technical roadblock.