looping through all days of the year [Archive]

Ryan Randall

02-01-02, 10:38

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 d

Ryan Randall

02-01-02, 12:11

Just 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