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