CODEWITHSUNDEEP
sqlsql-serverssrs-2008-r2
user10513216
user10513216

Reputation:

How can I order months given as strings in SSRS

How can I get months (in string) ordered in a SSRS query. I use month as parameter in a drop-down list.

This code seems to not be working.

SELECT DATENAME(month, DATEADD(month, 6, getdate())) AS MonthName

UNION

SELECT DATENAME(month, DATEADD(month, 7, getdate())) AS MonthName

UNION

SELECT DATENAME(month, DATEADD(month, 8, getdate())) AS MonthName

UNION

SELECT DATENAME(month, DATEADD(month, 9, getdate())) AS MonthName

ORDER BY
  CASE MonthName
     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
     ELSE 0
  END

Upvotes: 0

Views: 85

Answers (3)

Farmer
Farmer

Reputation: 19

I'm not sure about what you expected but anyway you can try:

select * from
(SELECT DATENAME(month, DATEADD(month, 6, getdate())) AS MonthName
UNION
SELECT DATENAME(month, DATEADD(month, 7, getdate())) AS MonthName
UNION
SELECT DATENAME(month, DATEADD(month, 8, getdate())) AS MonthName
UNION
SELECT DATENAME(month, DATEADD(month, 9, getdate())) AS MonthName
) a
ORDER BY
CASE a.MonthName
 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
 ELSE 0
END

Upvotes: 1

Thom A
Thom A

Reputation: 95564

Simply put, don't do it that way. If you want to order as they appear in the calendar, not alphabetically, you need to be able to access that data:

SELECT DATENAME(MONTH, DATEADD(MONTH, V.I,GETDATE())) AS [MonthName]
FROM (VALUES(6),(7),(8),(9)) V(I)
ORDER BY V.I;

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1269633

I would simplify this using VALUES. Using your logic:

SELECT v.MonthName
FROM (VALUES (DATENAME(month, DATEADD(month, 6, getdate()))), 
             (DATENAME(month, DATEADD(month, 7, getdate()))),
             (DATENAME(month, DATEADD(month, 8, getdate()))),
             (DATENAME(month, DATEADD(month, 9, getdate())))
     ) V(MonthName)
ORDER BY
  CASE MonthName
     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
     ELSE 0
  END;

However, you can simplify this further to:

SELECT v.MonthName
FROM (VALUES (DATENAME(month, DATEADD(month, 6, getdate()))), 
             (DATENAME(month, DATEADD(month, 7, getdate()))),
             (DATENAME(month, DATEADD(month, 8, getdate()))),
             (DATENAME(month, DATEADD(month, 9, getdate())))
     ) V(MonthName)
ORDER BY cast(MonthName + '1, 2000' as date);

Upvotes: 0

Related Questions