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
