Select Dates Between Two Column Values

Question

If I have a table with a StartDate column and an EndDate column can I produce a query that returns a set including every day in the range. I could use a table variable and do some procedural code but I'd like to know if there's a way to do it in a query.

E.g. StartDate = 1/1/2010, EndDate = 1/5/2010, result would be:

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

...for every row in the table that has the StartDate and EndDate columns.

*I'm on SQL 2005

Answer 1

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) 

Answer 2

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')