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

OMG Ponies · Accepted Answer · 2010-01-14 20:43:12Z

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

answered Jan 14, 2010 at 20:43

OMG Ponies

334k85 gold badges536 silver badges508 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

Aaronaught Over a year ago

Using a recursive CTE to generate a sequence: EVIL. But you did list the spt_values approach, so I'll give you the point.

OMG Ponies Over a year ago

@Aaronaught: The recursive CTE is slightly more efficient than using the NUMBERS table trick. Dunno about SPT_VALUES

Hogan Over a year ago

@Aaronaught: why is it evil -- tail end recursion is just a loop.

Aaronaught Over a year ago

@Hogan: Tail recursion can be transformed into a loop, but that doesn't mean it always is. Setting OPTION (MAXRECURSION 10) (or similar) will cause this to fail, which says to me at least that it is in fact being executed recursively.

George Johnston · Accepted Answer · 2010-01-14 20:39:40Z

0

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)

edited Jan 14, 2010 at 20:39

answered Jan 14, 2010 at 20:31

George Johnston

32.3k29 gold badges130 silver badges176 bronze badges

1 Comment

Russell Steen Over a year ago

that's not what he is asking. He wants a list of all calendar dates between the start date and end date, not dates in his table.

Collectives™ on Stack Overflow

Select Dates Between Two Column Values

2 Answers 2

4 Comments

1 Comment

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

4 Comments

1 Comment

Related