Choose dates between two column values

Question

If I have a table with a StartDate column and an EndDate column, I can create a query that returns a set that includes every day in the range. I could use a table variable and do some procedural code, but I would like to know if there is a way to do this in the query.

eg. StartDate = 1/1/2010, EndDate = 1/5/2010, the result will be:

• 1/1/2010
• 1/2/2010
• 1/3/2010
• 1/4/2010
• 1/5/2010

... for each row of the table in which there are StartDate and EndDate columns.

* I'm on SQL 2005

Answer 1

SQL Server 2005 +:

WITH dates AS (
   SELECT t.startdate 'date'
     FROM TABLE t
    WHERE t.startdate = '1/1/2010'
   UNION ALL
   SELECT DATEADD(dd, 1, t.date) 
     FROM dates t
    WHERE DATEADD(dd, 1, t.date) <= (SELECT t.enddate FROM TABLE t WHERE t.enddate = '1/5/2010'))
SELECT ...
  FROM TABLE t
  JOIN dates d ON d.date = t.date

If your dates are no more than 2047 days apart:

SELECT DATEADD(day, 
               n.number, 
               (SELECT t.startdate FROM TABLE t WHERE t.startdate = '1/1/2010')
               )
  FROM (SELECT DISTINCT number 
          FROM MASTER.dbo.SPT_VALUES
         WHERE name IS NULL) n
 WHERE DATEADD(day, n.number, (SELECT t.startdate FROM TABLE t WHERE t.startdate = '1/1/2010')) <= (SELECT t.endate FROM TABLE t WHERE t.endate = '1/5/2010')

Answer 2

with DateList as 
( 
      select cast('1/1/2010' as datetime) DateValue 
      union all 
      select DateValue + 1 
      from    DateList     
      where   DateValue + 1 >= '1/1/2010' AND DateValue +1 <= '1/5/2010' 
) 
select CONVERT(varchar, DateValue, 101) 
from    DateList 

OPTION (MAXRECURSION 0)