Add a record for every day of a given date range

declare @Months int = -5 --variable defined to go back number of months one wanted

declare @StartDate date = DATEADD(d,1,DATEADD(MONTH, DATEDIFF(MONTH, -1, GETDATE())+@Months, -1)) -- to get first day of the month

declare @EndDate date = DATEADD(MONTH, DATEDIFF(MONTH, -1, GETDATE()),-1); -- to get last day of the month

WITH Dates([Date]) AS (

--Select First day in range

SELECT CONVERT(DATETIME,@StartDate) AS [Date]

UNION ALL

--Add a record for every day in the range

SELECT DATEADD(DAY, 1, [Date]) FROM Dates WHERE Date < CONVERT(DATETIME,@EndDate))

select * from Dates Option (maxrecursion 0)

Without Option (maxrecursion 0) will result in error, as the maximum recursion by default allowed in a CTE is 100.