-- select * from [dbo].[GenDateTimes]( 'year', '1/1/0001', '12/12/9999' ) order by [Date];
-- select * from [dbo].[GenDateTimes]( 'month', '1/1/0001', '12/12/9999' ) order by [Date];
-- select * from [dbo].[GenDateTimes]( 'week', '1/1/0001', '12/12/9999' ) order by [Date];
-- select * from [dbo].[GenDateTimes]( 'day', '1/1/0001', '12/12/9999' ) order by [Date];
-- select * from [dbo].[GenDateTimes]( 'hour', '1/1/0001', '12/12/9999' ) order by [Date];
-- select * from [dbo].[GenDateTimes]( 'minute', '1/1/1999', '12/12/2999' ) order by [Date];
-- select * from [dbo].[GenDateTimes]( 'second', '1/1/1999', '12/12/1999' ) order by [Date];
-- select * from [dbo].[GenDateTimes]( 'millisecond', '1/1/1999', '1/12/1999' ) order by [Date];
create or alter function [dbo].[GenDateTimes](
@Increment varchar(11),
@Start datetime2(3),
@End datetime2(3)
)
returns @Range table( [Date] datetime2(3) )
as
begin;
declare @units bigint =
case
when @Increment = 'ms' THEN datediff_big( ms, @Start, @End )
when @Increment = 'millisecond' THEN datediff_big( millisecond, @Start, @End )
when @Increment = 's' THEN datediff_big( s, @Start, @End )
when @Increment = 'second' THEN datediff_big( second, @Start, @End )
when @Increment = 'mi' THEN datediff_big( mi, @Start, @End )
when @Increment = 'minute' THEN datediff_big( minute, @Start, @End )
when @Increment = 'hh' THEN datediff_big( hh, @Start, @End )
when @Increment = 'hour' THEN datediff_big( hour, @Start, @End )
when @Increment = 'd' THEN datediff( d, @Start, @End )
when @Increment = 'day' THEN datediff( day, @Start, @End )
when @Increment = 'wk' THEN datediff( wk, @Start, @End )
when @Increment = 'week' THEN datediff( week, @Start, @End )
when @Increment = 'mm' THEN datediff( mm, @Start, @End )
when @Increment = 'month' THEN datediff( month, @Start, @End )
when @Increment = 'y' THEN datediff( y, @Start, @End )
when @Increment = 'year' THEN datediff( year, @Start, @End )
end;
set @Increment = lower( @Increment );
with
[l0] as ( select 0 [c] union all select 1 ),
[l1] as ( select 1 [c] from [l0] [a] cross join [l0] [b] ),
[l2] as ( select 1 [c] from [l1] [a] cross join [l1] [b] ),
[l3] as ( select 1 [c] from [l2] [a] cross join [l2] [b] ),
[l4] as ( select 1 [c] from [l3] [a] cross join [l3] [b] ),
[l5] as ( select 1 [c] from [l4] [a] cross join [l4] [b] ),
[nums] as ( select row_number() over( order by ( select null ) ) [n] from [l5] ),
[cte]( [Date] ) AS (
select @Start [Date]
union all
select
case
when @Increment = 'ms' THEN dateadd( ms, [n], @Start )
when @Increment = 'millisecond' THEN dateadd( millisecond, [n], @Start )
when @Increment = 's' THEN dateadd( s, [n], @Start )
when @Increment = 'second' THEN dateadd( second, [n], @Start )
when @Increment = 'mi' THEN dateadd( mi, [n], @Start )
when @Increment = 'minute' THEN dateadd( minute, [n], @Start )
when @Increment = 'hh' THEN dateadd( hh, [n], @Start )
when @Increment = 'hour' THEN dateadd( hour, [n], @Start )
when @Increment = 'd' THEN dateadd( d, [n], @Start )
when @Increment = 'day' THEN dateadd( day, [n], @Start )
when @Increment = 'wk' THEN dateadd( wk, [n], @Start )
when @Increment = 'week' THEN dateadd( week, [n], @Start )
when @Increment = 'mm' THEN dateadd( mm, [n], @Start )
when @Increment = 'month' THEN dateadd( month, [n], @Start )
when @Increment = 'y' THEN dateadd( y, [n], @Start )
when @Increment = 'year' THEN dateadd( year, [n], @Start )
end
from [nums] [t]
where [t].[n] <= @units
)
insert into @Range( [Date] )
select [Date]
from [cte];
return;
end;
[…] Please try the Improved version of this […]
LikeLike