Generating a Date Range With a Tally Table

Another way you can generate a range of dates is by utilizing a Tally Table to create the dates between the range:

Declare @FromDate Date = '2014-04-21',
        @ToDate Date = '2014-09-02'
;With
    E1(N) As (Select 1 From (Values (1), (1), (1), (1), (1), (1), (1), (1), (1), (1)) DT(N)),
    E2(N) As (Select 1 From E1 A Cross Join E1 B),
    E4(N) As (Select 1 From E2 A Cross Join E2 B),
    E6(N) As (Select 1 From E4 A Cross Join E2 B),
    Tally(N) As
    (
        Select Row_Number() Over (Order By (Select Null))
        From E6
    )
    Select DateAdd(Day, N - 1, @FromDate) Date
    From Tally
    Where N <= DateDiff(Day, @FromDate, @ToDate) + 1

 Related Article: Generating Date Range With Recursive CTE

Generating Date Range With Recursive CTE

 Using a Recursive CTE, you can generate an inclusive range of dates:

Declare @FromDate Date = '2014-04-21',
        @ToDate Date = '2014-09-02'
;With DateCte (Date) As
(
    Select @FromDate Union All
    Select DateAdd(Day, 1, Date)
    From DateCte
        Where Date < @ToDate
)
Select Date
    From DateCte
Option (MaxRecursion 0)

The default MaxRecursion setting is 100. Generating more than 100 dates using this method will require the Option. Otherwise it will throw below error.

The statement terminated. The maximum recursion 100 has been exhausted before statement completion.

(MaxRecursion N) segment of the query, where N is the desired MaxRecursion setting. Setting this to 0 will remove the MaxRecursion limitation altogether.

Related Article : Generating a Date Range With a Tally Table