Tuesday, April 15, 2014

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


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..