Getting Dates between a range of dates

Question

I need to get all the dates present in the date range using SQL Server 2005

Solution 1

Here you go:

DECLARE @DateFrom smalldatetime, @DateTo smalldatetime;
SET @DateFrom='20000101';
SET @DateTo='20081231';
-------------------------------
WITH T(date)
AS
( 
SELECT @DateFrom 
UNION ALL
SELECT DateAdd(day,1,T.date) FROM T WHERE T.date < @DateTo
)
SELECT date FROM T OPTION (MAXRECURSION 32767);

Solution 2

If you have the dates in a table and simply want to select those between two dates you can use

select * from yourTable where yourDate between date1 and date2

If you want to produce the dates from nothing you could do it with a loop or you could populate a temporary table with dates and then select from that.

Solution 3

DECLARE @Date1 DATE='2016-12-21', @Date2 DATE='2016-12-25'
SELECT DATEADD(DAY,number,@Date1) [Date] FROM master..spt_values WHERE type = 'P' AND DATEADD(DAY,number,@Date1) <= @Date2

Solution 4

Here's Oracle version of date generation:

SELECT TO_DATE ('01-OCT-2008') + ROWNUM - 1 g_date
  FROM all_objects
 WHERE ROWNUM <= 15

instead of all_objects it can be any table with enough rows to cover the required range.

View more solutions

Comments

[Kevin Fairchild]

I dig that solution. Use something similar for timelines, so the charting keeps a consistent interval even without matching records for that time period in the database.

[Incidently]

Right. This works for generating any sequences: just replace DateAdd(day,1,T.date) with some other this_item=F(previous_item) formula

[jons911]

Very cool solution w/ the CTE. I hadn't seen this done before.

[RThomas]

This rocks. I needed something that would give me all the Mondays between the two dates. So I used this with an additional filter using a datepart and bam... exactly what I needed. +1 frome me.

[Greg Z.]

This only works for smaller date-ranges. Smaller than 2048 days.