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
21,025 total views, 1 views today