Getting Dates between a range of dates

Question

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

Answer 1

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.

Answer 2

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

Answer 3

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

Answer 4

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

select distinct(orderPlacedDate) from orders where orderPlacedDate between '2008-07-01' and 2008-09-30' order by orderPlacedDate

Answer 5

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.

Answer 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 new the total rows expected */
set @max = 999 

/* we are building the NUMBERS table on the fly */
/* but but this could be a proper table in the database */
/* created at the point of 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