Find overlapping time ranges

Question

I'm surprised this hasn't come up yet.

In T-SQL, I need to find the intervals (defined by startDateTime and endDateTime) that overlap with daily interval (say 9am-5pm).

For example, with table:

CREATE TABLE [dbo].[Interval](
    [startDateTime] [datetime] NOT NULL,
    [endDateTime] [datetime] NOT NULL
)

Solution would be the procedure that returns only overlapping intervals:

CREATE PROCEDURE FindIntervals
    -- Add the parameters for the stored procedure here
    @from varchar(5) = '9:00', 
    @to varchar(5) = '17:00'
AS
BEGIN
    select * from Interval
    where ...
END
GO

EDIT: Example intervals:

Sep 7 2011 8:00 AM - Sep 7 2011 8:30 PM
Sep 7 2011 11:00 AM - Sep 7 2011 1:00 PM
Sep 7 2011 1:00 PM - Sep 7 2011 6:00 PM
Sep 9 2011 8:00 AM - Sep 9 2011 8:30 PM
Sep 9 2011 11:00 AM - Sep 9 2011 1:00 PM
Sep 9 2011 1:00 PM - Sep 9 2011 6:00 PM

So, for given interval "nine to five", 2, 3, 5 and 6 should be returned, as they overlap the given input.

But,

Sep 9 2011 8:00 AM - Sep 10 2011 8:30 PM

also fits, because it includes entire day.

Please, I need help with matching string and datetime values in T-SQL, not abstract "less then"/"greater then" solutions.

[It has definitely come up before...](http://stackoverflow.com/questions/143552/comparing-date-ranges/143568#143568) — Martin Smith, Sep 28 '11 at 13:59
not really. Comparing strings with datetime values is far from trivial. the problem here is not the algorithm itself (which is trivial), but how to do it in T-SQL. — tishma, Sep 28 '11 at 14:10
I would have said the opposite. Once you know the algorithm it is trivial to implement in TSQL but from the linked questions on the dupe http://stackoverflow.com/q/4816323/73226 — Martin Smith, Sep 28 '11 at 14:11
sry dude, but you are totally missing it. i don't have a single interval that I'm trying to find overlapping intervals. it could be ANY DAY between 9 and 5. — tishma, Sep 28 '11 at 14:15
As far as I can see it is still only going to be a minor variation on any of those answers. Can you add some example data into your question so we can see exactly what you need? Also please state version of SQL Server — Martin Smith, Sep 28 '11 at 14:18
@MartinSmith - Perhaps this question was a duplicate in the early versions but I don't think it is that anymore. The question has evolved :). — Mikael Eriksson, Sep 28 '11 at 20:42
I hoped that procedure params were enough to make it unique, but Example definitely makes things clear. I'll post the complete solution that addresses multi-day intervals. Even more - SQL Server 2008 has Time datatype which promises in terms of performance over strings comparison... — tishma, Sep 28 '11 at 20:52

Mikael Eriksson · Accepted Answer · 2011-09-29T03:41:57.727

declare @Interval table
(
  startDateTime datetime,
  endDateTime datetime
)

insert into @Interval values
('2011-09-07T08:00:00', '2011-09-07T08:30:00'),
('2011-09-07T11:00:00', '2011-09-07T13:00:00'),
('2011-09-07T13:00:00', '2011-09-07T18:00:00'),
('2011-09-09T08:00:00', '2011-09-09T08:30:00'),
('2011-09-09T11:00:00', '2011-09-09T13:00:00'),
('2011-09-09T13:00:00', '2011-09-09T18:00:00'),
('2011-09-09T08:00:00', '2011-09-10T08:30:00')

declare @from varchar(5) = '09:00'
declare @to varchar(5) = '17:00'

;with L(MinDate, MaxDate) as
(
  select dateadd(day, datediff(day, 0, min(startDateTime)), 0),
         dateadd(day, datediff(day, 0, max(endDateTime)), 0)
  from @Interval
), 
D(fromTime, endTime) as
(
  select dateadd(day, Number.number, L.MinDate)+cast(@from as datetime),
         dateadd(day, Number.number, L.MinDate)+cast(@to as datetime)
  from L
    inner join master..spt_values as Number
      on Number.number <= datediff(day, L.MinDate, L.MaxDate)
  where Number.type = 'P'
)
select I.startDateTime,
       I.endDateTime
from @Interval as I
where exists (select *
              from D
              where I.startDateTime < D.endTime and
                    I.endDateTime > D.fromTime)

Result:

startDateTime           endDateTime
----------------------- -----------------------
2011-09-07 11:00:00.000 2011-09-07 13:00:00.000
2011-09-07 13:00:00.000 2011-09-07 18:00:00.000
2011-09-09 11:00:00.000 2011-09-09 13:00:00.000
2011-09-09 13:00:00.000 2011-09-09 18:00:00.000
2011-09-09 08:00:00.000 2011-09-10 08:30:00.000

If you expect to have a date range of more than 2048 days you need to replace master..spt_values with a numbers table. Make sure the numbers table starts with 0.

SQL Server 2008 version

;with L(MinDate, MaxDate) as
(
  select cast(min(startDateTime) as date),
         cast(max(endDateTime) as date)
  from @Interval
), 
D(fromTime, endTime) as
(
  select dateadd(day, Number.number, L.MinDate)+cast(@from as datetime),
         dateadd(day, Number.number, L.MinDate)+cast(@to as datetime)
  from L
    inner join master..spt_values as Number
      on Number.number <= datediff(day, L.MinDate, L.MaxDate)
  where Number.type = 'P'
)
select I.startDateTime,
       I.endDateTime
from @Interval as I
where exists (select *
              from D
              where I.startDateTime < D.endTime and
                    I.endDateTime > D.fromTime)

+1 for convert suggestion. it isn't 100% perfect, still good enough. thanks. — tishma, Sep 28 '11 at 15:50
@tishma - Not only that. It also "addresses multi-day intervals". And in SQL Server 2008 with time data type the solution is exactly the same. — Mikael Eriksson, Sep 29 '11 at 03:42

Find overlapping time ranges

1 Answers1