SQL Server 2000 (creating all dates given a daterange)

Question

Hi, I was wondering if there is a way in SQL Server 2000 to create all dates given a start and end date as a result. I know I can achieve this with T-SQL looping. I am looking for a non looping solution. Also in 2005 you can use the recursive with clause. The solution can also be using a T table that has numbers in it to join with the table. Again I am looking at a SQL Server 2000 non looping/using T tables solution. Is there any?

Answer 1

SELECT
     DATEADD(dy, T.number, @start_date)
FROM
     T
WHERE
     number BETWEEN 0 AND DATEDIFF(dy, @start_date, @end_date)

A Calendar table can also be useful for these kind of queries and you can add some date-specific information to it, such as whether a day is a holiday, counts as a "business" day, etc.

Answer 2

try this:

create numbers table, only need to do this one time in your DB:

CREATE TABLE Numbers (Number int NOT NULL)
GO
ALTER TABLE Numbers ADD CONSTRAINT PK_Numbers PRIMARY KEY CLUSTERED (Number)
GO
DECLARE @x int
set @x=0
WHILE @X<8000
BEGIN
    SET @X=@X+1
    INSERT INTO Numbers VALUES (@x)
END

--run your query:

DECLARE @StartDate datetime
DECLARE @EndDate   datetime

set @StartDate='05/03/2009'
set @EndDate='05/12/2009'

SELECT
    @StartDate+Number-1
    FROM Numbers
    WHERE Number<=DATEDIFF(day, @StartDate, @EndDate)+1