4

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 ?

Yegor Razumovsky
  • 902
  • 2
  • 9
  • 26

1 Answers1

2

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);
TechDo
  • 18,398
  • 3
  • 51
  • 64
  • awesome. And no master.dbo.spt_values magic !!! But what is OPTION (MAXRECURSION 0); ? – Yegor Razumovsky Dec 03 '13 at 12:16
  • 1
    @YegorRazumovsky - Specifies the maximum number of recursions allowed for this query. number is a nonnegative integer between 0 and 32767. When 0 is specified, no limit is applied. If this option is not specified, the default limit for the server is 100 ([Source](http://technet.microsoft.com/en-us/library/ms181714.aspx)) – Hans Kesting Dec 03 '13 at 15:05