I literally had to spend around an hour to crack this code, with some little help from my best geek friend, GOOGLE, of course.

Issue is this: I wanted to get Month in Numbers (Jan = 1, Feb = 2, etc.) that exist between two dates. For instance, if my date range is 1-Jan-2011 to 31-Mar-2011, then I should find and retrieve the months Jan, Feb and Mar as 1, 2 and 3 respectively.

After much much toiling and reading some stuff online, the following is what I got for myself:

—–

;WITH Numbers (Number) AS
(SELECT ROW_NUMBER() OVER (ORDER BY OBJECT_ID) FROM sys.all_objects)
SELECT MONTH(DATEADD(MONTH, Number – 1, ‘2011-01-01’)) Month_Number
FROM Numbers
WHERE Number – 1 <= DATEDIFF(MONTH, ‘2011-01-01’, ‘2011-03-31’)

—–

IMPORTANT: Make sure that you have more than 12 records in sys.all_objects table, which by default SHOULD have.

I hope this code piece is useful for some who have got similar requirement.

VAIDY