Generate calendar table in SQL Server

How to display dates, or create a calendar table for any n numbers of years in SQL Server?

It can be done easily through using Recursive CTE. Following T-SQL query uses CTE to generate calendar date.

Here, we have passed year to variable @Year as 2019 and year count to variable @YearCnt as 2, which means query will start generating dates from year 2019 till year 2020.

DECLARE @Year INT = '2019';
DECLARE @YearCnt INT = 2 ;
DECLARE @StartDate DATE = DATEFROMPARTS(@Year, '01,'01')
DECLARE @EndDate DATE = DATEADD(DAY, -1, DATEADD(YEAR, @YearCnt, 
@StartDate));

;WITH Cal(n) AS
(
SELECT 0 UNION ALL SELECT n + 1 FROM Cal
WHERE n < DATEDIFF(DAY, @StartDate, @EndDate)
),
FnlDt(d) AS
(
SELECT DATEADD(DAY, n, @StartDate) FROM Cal
),
FinalCte AS
(
SELECT
[Date] = CONVERT(DATE,d),
[Day] = DATEPART(DAY, d),
[Month] = DATENAME(MONTH, d),
[Year] = DATEPART(YEAR, d),
[DayName] = DATENAME(WEEKDAY, d)

FROM FnlDt
)
SELECT * FROM finalCte
ORDER BY [Date]
OPTION (MAXRECURSION 0);

 

*****************************

*****************************

As you can see above output, it generates date from 1st Jan 2019 till 31st Dec 2020.

SQL Server Interview questions