Using a Recursive CTE, you can generate an inclusive range of dates:
Declare @FromDate Date = '2014-04-21', @ToDate Date = '2014-05-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)
MaxRecursion setting is 100. Generating more than 100 dates using this method will require the
Option (MaxRecursion N) segment of the query, where
N is the desired
MaxRecursion setting. Setting this to
0 will remove the
MaxRecursion limitation altogether.