Forum Discussion

Copper Contributor

Jul 02, 2023

repeat records in a script

dears, good day!! i have a script where my date column is convert (varchar(6),getdate(),112) as Mnth_code and i want to repeat all records till the end of the year current month is 202307 i...

olafhelper

Bronze Contributor

Jul 03, 2023

ArarA911 , use a recursive CTE.

Important is MAXRECURSION, default is max 100 and so without it would fail

;WITH cte AS
    (SELECT convert (date, GETDATE(), 112) AS MyDate
	 UNION ALL
	 SELECT DATEADD(day, 1, cte.MyDate)
	 FROM cte
	 WHERE cte.MyDate < '20231231')
SELECT *
FROM cte
OPTION (MAXRECURSION 366);