How can I order months given as strings in SSRS

Question

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

I'll be straight. My point was that "not working" doesn't say too much. Instead of that add the error message if it fails or actual and expected results to the question as in that case people helping you will spend the time on giving you answer rather than understanding what exactly did you mean. — Dmitrij Kultasev, Oct 17 '18 at 14:43

score 1 · Answer 1 · answered Oct 17 '18 at 11:28

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;

score 1 · Answer 2 · answered Oct 17 '18 at 11:28

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

score 0 · Answer 3 · answered Oct 17 '18 at 11:27

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);

How can I order months given as strings in SSRS

3 Answers3