-- select * from [dbo].[GenDates2]( 'd', getdate()-16383, getdate()+16384 );
-- select * from [dbo].[GenDates2]( 'w', getdate()-16383, getdate()+16384 );
-- select * from [dbo].[GenDates2]( 'm', getdate()-16383, getdate()+16384 );
-- select * from [dbo].[GenDates2]( 'y', getdate()-1, getdate()-16384 );
create or alter function [dbo].[GenDates2](
@Increment char(1),
@Start datetime2(3),
@End datetime2(3)
)
returns @Range table( [Date] datetime2(3), [Count] int )
as
begin;
set @Increment = lower( @Increment );
with cte( [Date], [Count] ) AS (
select @Start [Date], 0 [Count]
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,
case
when @Increment = 'd' THEN datediff( day, @Start, dateadd(day, 1, [Date]))
when @Increment = 'w' THEN datediff(week, @Start, dateadd(week, 1, [Date]))
when @Increment = 'm' THEN datediff(month, @Start, dateadd(month, 1, [Date]))
when @Increment = 'y' THEN datediff(year, @Start, dateadd(year, 1, [Date]))
end [Count]
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], [Count] )
select [Date], [Count]
from [cte]
option (maxrecursion 32767);
return;
end;