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