I need to get all the dates present in the date range using SQL Server 2005
-
Do you want to generate the dates, or just search an existing field? – Unsliced Nov 07 '08 at 10:08
-
3Harish, Please mark one of the replies below as an answer. They took time to help you. – FMFF Aug 28 '13 at 19:01
7 Answers
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);

- 4,249
- 3
- 23
- 30
-
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. – Kevin Fairchild Nov 07 '08 at 15:30
-
1Right. This works for generating any sequences: just replace DateAdd(day,1,T.date) with some other this_item=F(previous_item) formula – Incidently Nov 07 '08 at 15:44
-
-
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. – RThomas Dec 22 '11 at 22:33
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.

- 34,602
- 16
- 110
- 145
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

- 49,044
- 25
- 144
- 182

- 161
- 2
- 2
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.

- 1,894
- 1
- 11
- 6
Slightly more complicated but perhaps more flexible would be to make use of a table containing a sequential set of numbers. This allows for more than one date range with different intervals.
/* holds a sequential set of number ie 0 to max */
/* where max is the total number of rows expected */
declare @Numbers table ( Number int )
declare @max int
declare @cnt int
set @cnt = 0
/* this value could be limited if you knew the total rows expected */
set @max = 999
/* we are building the NUMBERS table on the fly */
/* but this could be a proper table in the database */
/* created at the point of first deployment */
while (@cnt <= @max)
begin
insert into @Numbers select @cnt
set @cnt = @cnt + 1
end
/* EXAMPLE of creating dates with different intervals */
declare @DateRanges table (
StartDateTime datetime, EndDateTime datetime, Interval int )
/* example set of date ranges */
insert into @DateRanges
select '01 Jan 2009', '10 Jan 2009', 1 /* 1 day interval */
union select '01 Feb 2009', '10 Feb 2009', 2 /* 2 day interval */
/* heres the important bit generate the dates */
select
StartDateTime
from
(
select
d.StartDateTime as RangeStart,
d.EndDateTime as RangeEnd,
dateadd(DAY, d.Interval * n.Number, d.StartDateTime) as StartDateTime
from
@DateRanges d, @Numbers n
) as dates
where
StartDateTime between RangeStart and RangeEnd
order by StartDateTime
I actully use a variation of this to split dates into time slots (with various intervals but usually 5 mins long). My @numbers table contains a max of 288 since thats the total number of 5 min slots you can have in a 24 hour period.
/* EXAMPLE of creating times with different intervals */
delete from @DateRanges
/* example set of date ranges */
insert into @DateRanges
select '01 Jan 2009 09:00:00', '01 Jan 2009 12:00:00', 30 /* 30 minutes interval */
union select '02 Feb 2009 09:00:00', '02 Feb 2009 10:00:00', 5 /* 5 minutes interval */
/* heres the import bit generate the times */
select
StartDateTime,
EndDateTime
from
(
select
d.StartDateTime as RangeStart,
d.EndDateTime as RangeEnd,
dateadd(MINUTE, d.Interval * n.Number, d.StartDateTime) as StartDateTime,
dateadd(MINUTE, d.Interval * (n.Number + 1) , StartDateTime) as EndDateTime
from
@DateRanges d, @Numbers n
) as dates
where
StartDateTime >= RangeStart and EndDateTime <= RangeEnd
order by StartDateTime

- 18,029
- 7
- 45
- 66
To generate a range of dates you could write a table-valued function. This is a function that creates a date dimension for a data warehouse - you could probably adapt it fairly readily by trimming out the specials.
Edit: Here it is without the date dimension hierarchy.
if object_id ('ods.uf_DateHierarchy') is not null
drop function ods.uf_DateHierarchy
go
create function ods.uf_DateHierarchy (
@DateFrom datetime
,@DateTo datetime
) returns @DateHierarchy table (
DateKey datetime
) as begin
declare @today datetime
set @today = @Datefrom
while @today <= @DateTo begin
insert @DateHierarchy (DateKey) values (@today)
set @today = dateadd (dd, 1, @today)
end
return
end
go

- 64,444
- 15
- 143
- 197
If what you want is to get all dates present in your database between two dates (i.e. what dates have customers placed orders in Q3 of 2008) you would write something like this:
select distinct(orderPlacedDate)
from orders
where orderPlacedDate between '2008-07-01' and 2008-09-30'
order by orderPlacedDate