Update: Please try the Improved version of this script.
-- select * from [dbo].[GenDates]( 'd', getdate()-16383, getdate()+16384 ); -- select * from [dbo].[GenDates]( 'w', getdate()-16383, getdate()+16384 ); -- select * from [dbo].[GenDates]( 'm', getdate()-16383, getdate()+16384 ); -- select * from [dbo].[GenDates]( 'y', getdate()-16383, getdate()+16384 ); create or alter function [dbo].[GenDates]( @Increment char(1), @Start datetime2(3), @End datetime2(3) ) returns @Range table( [Date] datetime2(3) ) as begin; set @Increment = lower( @Increment ); with [cte]( [Date] ) AS ( select @Start union all select case when @Increment = 'd' THEN dateadd(day, 1, [Date]) when @Increment = 'w' THEN dateadd(week, 1, [Date]) when @Increment = 'm' THEN dateadd(month, 1, [Date]) when @Increment = 'y' THEN dateadd(year, 1, [Date]) end from [cte] where [Date] <= case when @Increment = 'd' THEN dateadd(day, -1, @End) when @Increment = 'w' THEN dateadd(week, -1, @End) when @Increment = 'm' THEN dateadd(month, -1, @End) when @Increment = 'y' THEN dateadd(year, -1, @End) end ) insert into @Range( [Date] ) select [Date] from [cte] option (maxrecursion 32767); return; end;