Sort months in chronological order in SSRS

Question

Post reply

Sort months in chronological order in SSRS

pwalter83

SSChampion

Points: 14575
More actions
May 24, 2011 at 2:15 am

#149103

Hello,
I have a requirement where I need to sort the months in chronological sequence. This is actually a parameter where the months would be displayed in the drop down list. I have tried doing this thru the following code but it does not get the desired result:
-------------------------------------------------------------------------------
SELECT SUBSTRING(CONVERT(VARCHAR(11), create_dt, 113), 4, 8) AS create_dt
FROM mf_forum
where create_dt >= '01-10-2010'
GROUP BY SUBSTRING(CONVERT(VARCHAR(11), create_dt, 113), 4, 8)
ORDER BY datepart(mm,SUBSTRING(CONVERT(VARCHAR(11), create_dt, 113), 4, 8))
--------------------------------------------------------------------------------
The result that I get is:
Jan 2011
Feb 2011
Mar 2011
Apr 2011
May 2011
Oct 2010
Nov 2010
Dec 2010
-----------------------------------------
But this is what I want to achieve:
Oct 2010
Nov 2010
Dec 2010
Jan 2011
Feb 2011
Mar 2011
Apr 2011
May 2011
-----------------------------------------------
Could someone please tell me what I need to change in the code to achieve the above result ?
Thanks and Regards,
Paul

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply

LutzM SSC Guru Points: 107049 More actions · Answer 1

ORDER BY CAST(SUBSTRING(CONVERT(VARCHAR(11), date, 113), 4, 8) AS DATETIME)

Lutz
A pessimist is an optimist with experience.

How to get fast answers to your question[/url]
How to post performance related questions[/url]
Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

nigel. SSChampion Points: 11636 More actions · Answer 2

Wouldn't it just be:

ORDER BY create_dt

Nigel
How to post questions

nigel. SSChampion Points: 11636 More actions · Answer 3

nigel. (5/25/2011)
Wouldn't it just be:
ORDER BY create_dt

No, and I've just realised why, sorry :blush:

Too early, need more coffee

Nigel
How to post questions

pwalter83 SSChampion Points: 14575 More actions · Answer 4

LutzM (5/24/2011)
ORDER BY CAST(SUBSTRING(CONVERT(VARCHAR(11), date, 113), 4, 8) AS DATETIME)

You are the Man, Lutz !!!!! Genius !!!

Thanks very much !!!

pwalter83 SSChampion Points: 14575 More actions · Answer 5

LutzM (5/24/2011)
ORDER BY CAST(SUBSTRING(CONVERT(VARCHAR(11), date, 113), 4, 8) AS DATETIME)

Hi Lutz,

I was stuck with another issue and your solution resolved that one as well. You hit two birds with one stone !!!! Thanks a lot again !!!.

This was the initial problem:

http://www.sqlservercentral.com/Forums/Topic1113899-149-1.aspx#bm1114034

Regards,

Paul

LutzM SSC Guru Points: 107049 More actions · Answer 6

Glad I could help 😀

Lutz
A pessimist is an optimist with experience.

How to get fast answers to your question[/url]
How to post performance related questions[/url]
Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 7

When I can't port the datetime column in the report I simply do group and order by YEAR, then MONTH. But I guess that concatenating that into a single column should make for a lighter load on SSRS! Maybe I'll test someday ;-).

pwalter83 SSChampion Points: 14575 More actions · Answer 8

LutzM (5/25/2011)
Glad I could help 😀

Could I please ask you for another favor ? I am stuck with this one:

http://www.sqlservercentral.com/Forums/Topic1115231-149-1.aspx

Lutz, Your help would be deeply appreciated.

Thanks and Regards,

Paul