Dates in a SQL Command

hi,

i have to return all the dates in a date interval, but they are not stored in a table. what i need is to calculate all the dates between 2 given dates.

is it possible to do this in a SQL command ?

thanks,

levogiro.

levogiro:

One way to do this is to loop through a table of numbers. Here the table "small_iterator" is simply a table of integers 1-32767:

declare @.dayDiff integer
declare @.firstDate datetime set @.firstDate = '12/1/6'
declare @.secondDate datetime set @.secondDate = '1/3/7'

select @.dayDiff = datediff (day, @.firstDate, @.secondDate)
--select @.dayDiff as [@.dayDiff]

select convert (varchar (10), @.firstDate + iter - 1, 101) as rangeDate
from small_iterator (nolock)
where iter <= @.dayDiff + 1

-- Sample Output:

-- rangeDate
-- -
-- 12/01/2006
-- 12/02/2006
-- ...
-- 01/03/2007

Another solution is to use a calendar table as described in this article:

http://sqlserver2000.databases.aspfaq.com/why-should-i-consider-using-an-auxiliary-calendar-table.html

This article describes the use of a "Numbers" table:

http://sqlserver2000.databases.aspfaq.com/why-should-i-consider-using-an-auxiliary-numbers-table.html

I hope this helps.

Dave