-- 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;
Categories
One reply on “Improved SQL Function to Generate DateTimes”
[…] Please try the Improved version of this […]
LikeLike