I too wrote a slightly different version... Same binary concept, implemented slightly differently with a twist! It allows the specification of an increment or step value.
CREATE FUNCTION dbo.udf_genDateSequence ( @prmLoDate datetime, -- lower date boundary (starting value) @prmHiDate datetime, -- upper date boundary (maximum value) @prmIncDays int -- increment value ) RETURNS @Dates TABLE (DateVal datetime NOT NULL PRIMARY KEY) AS /*
Function: Generate a table of dates.
Strategy: Set-based scalar value generation.
Usage: dbo.udf_genDateSequence(fromValue, toValue, stepValue)
*/
BEGIN DECLARE @daysDiff int, @swapVar datetime
IF @prmLoDate > @prmHiDate BEGIN SET @swapVar = @prmLoDate SET @prmLoDate = DATEADD(day, DATEDIFF(day, 0, @prmHiDate), 0) SET @prmHiDate = DATEADD(day, DATEDIFF(day, 0, @swapVar), 0) END ELSE BEGIN SET @prmLoDate = DATEADD(day, DATEDIFF(day, 0, @prmLoDate), 0) SET @prmHiDate = DATEADD(day, DATEDIFF(day, 0, @prmHiDate), 0) END
SET @daysDiff = DATEDIFF(day, @prmLoDate, @prmHiDate)
INSERT INTO @Dates VALUES(@prmLoDate)
WHILE @prmIncDays <= @daysDiff BEGIN INSERT INTO @Dates SELECT DATEADD(day, @prmIncDays, DateVal) FROM @Dates WHERE DateVal <= DATEADD(day, -@prmIncDays, @prmHiDate)
SET @prmIncDays = @prmIncDays * 2 END
RETURN END go