Date Time Range or Calendar Generator

,

This function returns a formated calendar table. It came in handy a few times for me, so I thought it may be a good contribution. There would be a few better ways to create this function if date-part were a valid data type. It is a pretty straight-forward function. You may edit it if you wish to provide a start and end DTTM instead of start and time period I could not, unfortunately, do milliseconds, but I am assuming it a rare occasion you'd need to create a millisecond table.

CREATE FUNCTION fn_CalendarGenTable
(
	@DTTM datetime = null,
	@datePart char(2),
	@timeCount int,
	@timeInterval int = null
)
RETURNS @calendar table
	(
		DTTM datetime PRIMARY KEY NOT NULL
	)
AS
BEGIN
	SET @DTTM = ISNULL(@DTTM, '1900')
	SET @timeInterval = ISNULL(@timeInterval,1)
	
	DECLARE @counter int; SET @counter = 0

	WHILE @counter < @timeCount
	BEGIN

		INSERT INTO @calendar
		(DTTM)
		SELECT CASE @datePart
			WHEN 'yy'
			THEN DATEADD(yy, @counter * @timeInterval,@DTTM)
			WHEN 'yyyy'
			THEN DATEADD(yyyy, @counter * @timeInterval,@DTTM)
			WHEN 'q'
			THEN DATEADD(q, @counter * @timeInterval,@DTTM)
			WHEN 'qq'
			THEN DATEADD(qq, @counter * @timeInterval,@DTTM)
			WHEN 'mm'
			THEN DATEADD(mm, @counter * @timeInterval,@DTTM)
			WHEN 'm'
			THEN DATEADD(n, @counter * @timeInterval,@DTTM)
			WHEN 'dy'
			THEN DATEADD(dy, @counter * @timeInterval,@DTTM)
			WHEN 'y'
			THEN DATEADD(y, @counter * @timeInterval,@DTTM)
			WHEN 'dd'
			THEN DATEADD(dd, @counter * @timeInterval,@DTTM)
			WHEN 'd'
			THEN DATEADD(d, @counter * @timeInterval,@DTTM)
			WHEN 'wk'
			THEN DATEADD(wk, @counter * @timeInterval,@DTTM)
			WHEN 'ww'
			THEN DATEADD(ww, @counter * @timeInterval,@DTTM)
			WHEN 'hh'
			THEN DATEADD(hh, @counter * @timeInterval,@DTTM)
			WHEN 'mi'
			THEN DATEADD(mi, @counter * @timeInterval,@DTTM)
			WHEN 'n'
			THEN DATEADD(n, @counter * @timeInterval,@DTTM)
			WHEN 'ss'
			THEN DATEADD(ss, @counter * @timeInterval,@DTTM)
			WHEN 's'
			THEN DATEADD(s, @counter * @timeInterval,@DTTM)
			--cannot do ms, because violation of primary key
			--WHEN 'ms'
			--THEN DATEADD(ms,@counter,@DTTM)
			ELSE  null
		END

		SET @counter = @counter + 1
	END

	RETURN
END

Rate

Share

Share

Rate