Tag: datetime

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;