T-SQL Month Name,Number,Start and End of month

Hi,

Today's requirement is to create a month's table, to be used in a SSRS report parameters, to be passed to a stored procedure.

the parameters needs to have Month Name, Number, Abbreviation, Start and End of month.

Please find the T-sql code below:

;WITH     months (MonthNumber)

AS       (SELECT 1

          UNION ALL

          SELECT MonthNumber + 1

          FROM   months

          WHERE  MonthNumber < 12)

SELECT   MonthNumber,

         DATENAME(month, DATEADD(month, MonthNumber, -1)) AS [MonthName],

         UPPER(LEFT(DATENAME(month, DATEADD(month, MonthNumber, -1)), 3)) AS MonthAbbr,

         DATEADD(month, DATEDIFF(month, 0, CONVERT (VARCHAR (2), MonthNumber) + '/1/' + CONVERT (VARCHAR (4), YEAR(GETDATE()))), 0) AS StartOfMonth,

         DATEADD(d, -1, DATEADD(m, DATEDIFF(m, 0, CONVERT (VARCHAR (2), MonthNumber) + '/1/' + CONVERT (VARCHAR (4), YEAR(GETDATE()))) + 1, 0)) AS EndOfMonth

FROM     months

ORDER BY MonthNumber;  

Hope that helps..
Jason