How to select all dates from 2002 to 2013 in Microsoft Sql Server 2012

Question

I have found this solution, but it is limited to 2047 days.

DECLARE @MinDate DATE, @MaxDate DATE;
SELECT DATEADD(DAY,number,@MinDate) [Date]
    FROM master.dbo.spt_values
    WHERE type = 'P'
    AND number > 0
    AND DATEADD(DAY,number,@MinDate) <= @MaxDate

How can i extend maximum days count ?

Answer 1

Please try using CTE:

DECLARE @MinDate DATE, @MaxDate DATE;
SELECT @MinDate =DATEADD(year,-11 ,GETDATE()), @MaxDate =getdate();

;WITH Dates AS
(
    SELECT n = @MinDate
    UNION ALL
    SELECT DATEADD(day, 1, n)
    FROM Dates
    WHERE DATEADD(day, 1, n)  <= @MaxDate
)
SELECT n
FROM Dates 
OPTION (MAXRECURSION 0);