Microsoft SQL Server Common Table Expressions Generate a table of dates using CTE
Example
DECLARE @startdate CHAR(8), @numberDays TINYINT
SET @startdate = '20160101'
SET @numberDays = 10;
WITH CTE_DatesTable
AS
(
SELECT CAST(@startdate as date) AS [date]
UNION ALL
SELECT DATEADD(dd, 1, [date])
FROM CTE_DatesTable
WHERE DATEADD(dd, 1, [date]) <= DateAdd(DAY, @numberDays-1, @startdate)
)
SELECT [date] FROM CTE_DatesTable
OPTION (MAXRECURSION 0)
This example returns a single-column table of dates, starting with the date specified in the @startdate variable, and returning the next @numberDays worth of dates.
