Solved

# How to sort this kind of data containing month names

Posted on 2012-09-12
274 Views
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
Question by:searchsanjaysharma

LVL 82

Expert Comment

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 Comment

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

LVL 82

Expert Comment

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

LVL 32

Accepted Solution

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

LVL 32

Expert Comment

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

LVL 68

Expert Comment

ORDER BY
CHARINDEX (MonthField, 'JanuaryFebruaryMarchAprilMayJuneJulyAugustSeptemberOctoberNovemberDecember')
0

Author Closing Comment

Thanx
0

## Featured Post

I am showing a way to read/import the excel data in table using SQL server 2005... Suppose there is an Excel file "Book1" at location "C:\temp" with column "First Name" and "Last Name". Now to import this Excel data into the table, we will use…
When writing XML code a very difficult part is when we like to remove all the elements or attributes from the XML that have no data. I would like to share a set of recursive MSSQL stored procedures that I have made to remove those elements from …
Hi everyone! This is Experts Exchange customer support.  This quick video will show you how to change your primary email address.  If you have any questions, then please Write a Comment below!
This video discusses moving either the default database or any database to a new volume.