declare @dt1 date = '2000-01-01' ,@dt2 date = '2027-01-01' ;with cte as ( select dt = @dt1 union all select dt = dateadd(dd, 1, dt) from cte where dt < @dt2 ) select Dt from cte order by Dt option (maxrecursion 1000)