looping through all days of the year

I like nigelrivett's answer. My contribution is less efficient, but doesn't have a defined upper limit on the datediff.

It uses the table variable, so won't work in SQL 6.5 or 7.0 (you'll have to use a temporary table there - which has more drawbacks).

declare @.startdate datetime, @.enddate datetime
select @.startdate = '1 feb 2001', @.enddate = '20 sep 2010'

declare @.max_number int
set @.max_number = datediff(d, @.startdate, @.enddate)

--create a table of numbers from 0 to @.max_number
set nocount on
declare @.t table (i int)
insert into @.t values (0)

declare @.i int
set @.i = 1
while @.i < @.max_number/2
begin
insert into @.t select i + @.i from @.t
set @.i = @.i * 2
end
insert into @.t select i + @.i from @.t where i + @.i <= @.max_number
set nocount off

--use the table
select d = dateadd(dd, i, @.startdate) from @.t order by dJust for overkill, I've created a user-defined function which takes a number and returns a table of numbers from 0 to that number. That can then be used to solve this problem and elsewhere.

This will only work in SQL 2000 (as far as I know)...

CREATE FUNCTION table_of_numbers (@.max_number int)
RETURNS @.t table (i int)
AS
begin
insert into @.t values (0)

declare @.i int
set @.i = 1
while @.i < @.max_number/2
begin
insert into @.t select i + @.i from @.t
set @.i = @.i * 2
end
insert into @.t select i + @.i from @.t where i + @.i <= @.max_number

RETURN
end

So...

SELECT * FROM table_of_numbers(10)

Gives...

i
----
0
1
2
3
4
5
6
7
8
9
10

And for this specific problem...

declare @.startdate datetime, @.enddate datetime
select @.startdate = '1 feb 2001', @.enddate = '20 sep 2001'

declare @.max_number int
set @.max_number = datediff(d, @.startdate, @.enddate)
select d = dateadd(dd, i, @.startdate) from table_of_numbers(@.max_number) order by d