Using T-SQL to find events that overlap (or don't) in SQL Server

By:   |   Comments (2)   |   Related: > Dates


I often see people struggle with logic related to datetime range and interval queries. A common example is trying to determine if two events overlap, either fully or partially. Given an event that happens, did another event happen to cross that window in any way?

There are six different logical scenarios where two events could potentially be deemed to overlap, and many of the queries I see out in the wild treat all six of these scenarios separately. Only four of those scenarios might/can overlap. And when you boil it down, all four scenarios can be captured in two conditions. In this tip I'm going to show the six scenarios, and why two conditions capture all four of the actual overlapping scenarios.


The six scenarios I mentioned above are best described using a diagram. Given an event, Event1, with start time S1 and end time E1, and a second event as input, Event2, with start time @S2 and end time @E2, the possibilities are as follows:

6 ways 2 events can compare in terms of overlap

Treating Event1 as a window, the logic is determining whether Event2's window overlaps in any way. The first scenario represents the case where Event2 started and finished before Event1 started, so we can discard it, as this is not an overlap. The last scenario represents the case where Event2 started and finished after Event1 finished, so this is another we can discard. The remaining four scenarios – shaded in green – represent cases where Event2 started before or after Event1 started and Event2 finished before or after Event1 finished. For these scenarios, I often see four pairs of conditional logic, like:

WHERE (@S2 <  S1 AND @E2 >= S1) -- partial overlap at beginning
   OR (@S2 >= S1 AND @E2 <= E1) -- complete overlap
   OR (@S2 >  S1 AND @E2 <  E1) -- complete containment
   OR (@S2 <= E1 AND @E2 >  E1) -- partial overlap at end

But we can express it simpler than that, because all of these scenarios share two facts in common: Event2 finished after Event1 started *and* Event2 started before Event1 ended. Again, represented on a diagram:

Boiling it down to 2 actual criteria

All that really matters is that Event2 ends at the same time or after the start of Event1 – it doesn't matter if it ends on the early boundary, inside, on the later boundary, or after. Similarly, Event2 needs to start before or when Event1 ends – it doesn't matter if it starts before Event2, on the early boundary, inside, or on the later boundary.

This simplifies the logic to:

WHERE (@E2 >= S1 AND @S2 <= E1)

Now, how can we use this logic? Let's say we have a 5-minute period of high CPU activity, and we know that several backups took place around the same time, but we're not sure if they overlap.

We can check this by using the following:

DECLARE @S2 datetime2(0) = '2018-12-21 13:51:21', 
        @E2 datetime2(0) = '2018-12-21 13:56:21';
SELECT * -- for brevity, not best practice!
  FROM msdb.dbo.backupset
  WHERE @E2 >= backup_start_date
    AND @S2 <= backup_finish_date;

This should return all backups that either started during the CPU activity, ended during the CPU activity, or started before *and* ended after.

We can also do the inverse, and use this logic to ensure that an event does *not* take place inside the windows of existing events. Let's say we have a table full of training classes, and we want to book a new training class. We need to ensure that, before we create this new class, there aren't any existing classes that will conflict.

CREATE TABLE dbo.TrainingClasses
  ClassID   int,
  StartDate date,
  EndDate   date
INSERT dbo.TrainingClasses(ClassID, StartDate, EndDate)
 VALUES (1, '2019-01-07', '2019-01-10'),
        (2, '2019-01-14', '2019-01-16'),
        (3, '2019-01-17', '2019-01-18');

Now, I want to schedule a 3-day training class in January. I first try the 9th - 11th:

DECLARE @S2 date = '2019-01-09', @E2 date = '2019-01-11';
  SELECT 1 FROM dbo.TrainingClasses
  WHERE @E2 >= StartDate
    AND @S2 <= EndDate


0 row(s) affected.

Let's try again for two weeks later:

DECLARE @S2 date = '2019-01-23', @E2 date = '2019-01-25';
  SELECT 1 FROM dbo.TrainingClasses
  WHERE @E2 >= StartDate
    AND @S2 <= EndDate



I could change the logic around to avoid NOT EXISTS but, like DST and time zones, the fewer curveballs I throw myself, the better.


Solving for overlapping windows can be a challenging problem in any language. I hope the illustrations above help you see the problem space in a different way.

Next Steps

Read on for related tips and other resources:

sql server categories

sql server webinars

subscribe to mssqltips

sql server tutorials

sql server white papers

next tip

About the author
MSSQLTips author Aaron Bertrand Aaron Bertrand (@AaronBertrand) is a passionate technologist with industry experience dating back to Classic ASP and SQL Server 6.5. He is editor-in-chief of the performance-related blog,, and also blogs at

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips

Comments For This Article

Tuesday, September 24, 2019 - 11:17:43 AM - Aaron Bertrand Back To Top (82558)

Your code is more complex but it is the exact same logic as mine. Try this out for all 5 existing reservations:

CREATE TABLE #res(AirplaneID int, ResStart datetime, ResEnd datetime);

INSERT #res(AirplaneID, ResStart, ResEnd) VALUES
(1'20190924 10:00''20190924 11:00'), -- completely within -- returns 1
(2'20190924 08:00''20190924 09:30'), -- overlap begin -- returns 1
(3'20190924 09:30''20190924 12:30'), -- overlap end -- returns 1
(4'20190924 08:00''20190924 13:00'), -- completely surrounds -- returns 1
(5'20190924 13:00''20190924 14:30'); -- no overlap -- returns 0

DECLARE @AirplaneID  int = 1-- repeat for 2, 3, 4, 5
        @NewResStart datetime = '20190924 09:00'
        @NewResEnd   datetime = '20190924 12:00';

WHERE AirplaneID = @AirplaneID
  AND -- mylogic:
  (@NewResEnd >= ResStart
  AND @NewResStart <= ResEnd);

WHERE AirplaneID = @AirplaneID
AND -- your logic:
    (@NewResStart BETWEEN ResStart AND ResEnd)
    OR (@NewResEnd BETWEEN ResStart AND ResEnd)
    OR (ResStart  BETWEEN @NewResStart AND @NewResEnd)    
    OR (ResEnd  BETWEEN @NewResStart AND @NewResEnd)

Tuesday, September 24, 2019 - 10:36:21 AM - Dan DeFord Back To Top (82556)

This is really great and helped me with an issue I was struggling with - Thank you!

I see one small issue with your answer, though. In my use case, I'm writing an aircraft scheduling website using PHP and MySQL for the flying club I belong to. I want to ensure that a pilot can't schedule a specific aircraft at the same time as someone else is using it. It appears that if I were to use your solution as is, if someone had a plane reserved from 10am to 11am, and then I came along and tried to reserve the same plane from 9am to noon (e.g. my start and end times are outside of the window that the other pilot has it reserved) it would come back as 'OK'.

Here's what I ended up with. I opted to use a count rather than just 'OK' or not. It's easier in code to deal with an integer than text (e.g. if the integer is greater than 0, then a conflict exists - and it should never be more than 1):

SET @aircraft_id = '[AircraftTailNumber]';
SET @reservation_start  = '2019-09-22 09:00:00';
SET @reservation_end   = '2019-09-22 12:00:00';
SELECT COUNT(*) FROM reservations
WHERE aircraft_id=@aircraft_id 

    (@reservation_start BETWEEN reservation_start AND reservation_end)     OR (@reservation_end BETWEEN reservation_start AND reservation_end)     OR (reservation_start BETWEEN @reservation_start AND @reservation_end)         OR (reservation_end BETWEEN @reservation_start AND @reservation_end) );

Using this strategy, if there is a conflict where the previous reservation is completely within or completely encompasses my desired reservation, it will return a 1. Or if my start or end time are within a preor reservation, it will also return with a 1.

Obviously, using BETWEEN may cause some issues if a reservation ends at the same moment another begins (it's inclusive), but I'll deal with that in code.

Anyway, thanks again for your help. It got me on the right path.

get free sql tips
agree to terms