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;