Fast Date Ranges Without Loops In SQL Server 2000

The trick to create date ranges without loops is to use a pivot table.
How does this work? Run the code below and you will see, only create the pivot table once and run all the other code seperately

-- Create out Pivot table ** do this only once-- populate it with 1000 rows
CREATE TABLE NumberPivot (NumberID INT PRIMARY KEY)

DECLARE @intLoopCounter INT
SELECT @intLoopCounter =0

WHILE @intLoopCounter <=1000
BEGIN
INSERT INTO NumberPivot
VALUES (@intLoopCounter)

SELECT @intLoopCounter = @intLoopCounter +1
END
GO

--Last 10 years from today
SELECT DATEADD(yy,-numberID,GETDATE())
FROM dbo.NumberPivot
WHERE NumberID < 10

--Next 10 years from today
SELECT DATEADD(yy,numberID,GETDATE())
FROM dbo.NumberPivot
WHERE NumberID < 10

--Next 100 months from today
SELECT DATEADD(mm,numberID,GETDATE())
FROM dbo.NumberPivot
WHERE NumberID < 100

-- next 100 weeks from 2000-01-01
DECLARE @dtmDate DATETIME
SELECT @dtmDate = '2000-01-01 00:00:00.000'
SELECT DATEADD(wk,numberID,@dtmDate)
FROM dbo.NumberPivot
WHERE NumberID < 100

-- next 100 quarters from today
SELECT DATEADD(qq,numberID,GETDATE())
FROM dbo.NumberPivot
WHERE NumberID < 100

That's it, come back tomorrow and I will show you how to use the Pivot table to split strings