TSQL Script to generate Month Name and Number using recursive CTE

Rajnilari2015
Posted by Rajnilari2015 under Sql Server category on | Points: 40 | Views : 429
;WITH MonthCTE AS(
SELECT [MonthNumber]=1, [MonthName]=DateName(mm,DATEADD(mm,1 - 1,0))
UNION ALL
SELECT
[MonthNumber] = [MonthNumber] +1
,DateName(mm,DATEADD(mm,[MonthNumber]+1 - 1,0))
FROM MonthCTE
WHERE [MonthNumber] < 12

)
SELECT *
FROM MonthCTE


Result
-------
MonthNumber	MonthName
1 January
2 February
3 March
4 April
5 May
6 June
7 July
8 August
9 September
10 October
11 November
12 December

Comments or Responses

Login to post response