Categories
Date & Times Script SQL

Improved SQL Function to Generate DateTimes

-- 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;

One reply on “Improved SQL Function to Generate DateTimes”

Leave a Reply to SQL Function to Generate Date Table – Protiguous Software Cancel reply

Please log in using one of these methods to post your comment:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s