Home » Generate Calendar table in SQL Server

Generate Calendar table in SQL Server

How to Display dates 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



Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.