Detecting Date Range Overlaps in SQL Tables: A Practical Guide with T-SQL Examples

SQL programming often uses datetime fields to show when things start and end. But it can be tricky for developers when things overlap or happen at the same time. This SQL tutorial helps developers learn useful ways to detect and check these overlapping time periods, each with its own start and end times.

Create Event Table

Let's create an 'Event' table in T-SQL with three columns:

1. 'EventID': An integer column that will hold the unique identifier for each event. It's also marked as the primary key of the table.
2. 'StartDate': A datetime column that will store the start date of the event.
3. 'EndDate': A datetime column that will store the end date of the event.

Create an 'Event' table

CREATE TABLE Event (
    EventID INT IDENTITY(1, 1) PRIMARY KEY,
    StartDate DATETIME,
    EndDate DATETIME
);

Create Function to Check Overlapping Date Ranges

Let's create a T-SQL function that checks if the new event's start and end dates overlap with existing events in the 'Event' table.

The "CheckEventOverlap" function can take two input parameters representing a new event's start and end dates. It then performs SQL queries to check various conditions for overlaps with existing events in the 'Event' table. If any overlap is detected, the function returns 1; otherwise, it returns 0. This function can be used to determine whether a new event's date range overlaps with any existing events in the database.

Here are several ways of implementing the logics of checking date range overlaps in such a function. Solution 1 has the most complicated conditions in the WHERE clause. You could achieve the same thing with more concise WHERE clause using Solutions 2, 3, or 4.

Overlapping Date Ranges - Solution 1

You can use this Fiddle to follow along and practice checking date range overlaps using Solution 1

This SQL query calculates the count of overlapping events by checking four conditions:

1. EndDate falls within the range of the new event's start and end dates.
2. StartDate falls within the range of the new event's start and end dates.
3. The new event is entirely within the range of an existing event.
4. An existing event is entirely within the range of the new event.

Check Overlapping Date Ranges - Solution 1

CREATE FUNCTION CheckEventOverlap (
	@NewStartDate DATETIME
	,@NewEndDate DATETIME
	)
RETURNS BIT
AS
BEGIN
	DECLARE @OverlapCount INT;

	SELECT @OverlapCount = COUNT(*)
	FROM Event
	WHERE (
			EndDate BETWEEN @NewStartDate
				AND @NewEndDate
			)
		OR (
			StartDate BETWEEN @NewStartDate
				AND @NewEndDate
			)
		OR (
			@NewStartDate >= StartDate
			AND @NewEndDate <= EndDate
			)
		OR (
			@NewStartDate <= StartDate
			AND @NewEndDate >= EndDate
			);

	RETURN CASE
			WHEN @OverlapCount > 0
				THEN 1
			ELSE 0
			END;
END;

Overlapping Date Ranges - Solution 2

You can use this Fiddle to follow along and practice checking date range overlaps using Solution 2

This SQL query calculates the count of overlapping events by checking whether there are any events that do not satisfy the condition of not overlapping with the new event. In other words, it checks if the new event overlaps with any existing event.

Check Overlapping Date Ranges - Solution 2

CREATE FUNCTION CheckEventOverlap (
	@NewStartDate DATETIME
	,@NewEndDate DATETIME
	)
RETURNS BIT
AS
BEGIN
	DECLARE @OverlapCount INT;

	SELECT @OverlapCount = COUNT(*)
	FROM Event
	WHERE NOT (
			@NewStartDate > EndDate
			OR @NewEndDate < StartDate
			);

	RETURN CASE
			WHEN @OverlapCount > 0
				THEN 1
			ELSE 0
			END;
END;

Overlapping Date Ranges - Solution 3

You can use this Fiddle to follow along and practice checking date range overlaps using Solution 3

This SQL query calculates the count of overlapping events by checking if there are any events where both the new event's start date is not greater than the existing event's end date and the new event's end date is not less than the existing event's start date. In other words, it checks if the new event overlaps with any existing event.

Check Overlapping Date Ranges - Solution 3

CREATE FUNCTION CheckEventOverlap (
	@NewStartDate DATETIME
	,@NewEndDate DATETIME
	)
RETURNS BIT
AS
BEGIN
	DECLARE @OverlapCount INT;

	SELECT @OverlapCount = COUNT(*)
	FROM Event
	WHERE NOT @NewStartDate > EndDate
		AND NOT @NewEndDate < StartDate;

	RETURN CASE
			WHEN @OverlapCount > 0
				THEN 1
			ELSE 0
			END;
END;

Overlapping Date Ranges - Solution 4

You can use this Fiddle to follow along and practice checking date range overlaps using Solution 4

This SQL query calculates the count of overlapping events by checking if there are any events where the new event's start date is less than or equal to the existing event's end date, and the new event's end date is greater than or equal to the existing event's start date. In other words, it checks if the new event overlaps with any existing event.

Check Overlapping Date Ranges - Solution 4

CREATE FUNCTION CheckEventOverlap (
	@NewStartDate DATETIME
	,@NewEndDate DATETIME
	)
RETURNS BIT
AS
BEGIN
	DECLARE @OverlapCount INT;

	SELECT @OverlapCount = COUNT(*)
	FROM Event
	WHERE @NewStartDate <= EndDate
		AND @NewEndDate >= StartDate;

	RETURN CASE
			WHEN @OverlapCount > 0
				THEN 1
			ELSE 0
			END;
END;

In this function, @NewStartDate and @NewEndDate are the start and end dates of the new event you want to insert. The function checks if there are any existing events in the 'Event' table where the new event's date range overlaps with the existing event's date range.

The function returns 1 if an overlap is detected and 0 otherwise. You can use this function to determine whether you should insert a new event based on its start and end dates.

Create Stored Procedure to Insert Event If Date Ranges Do NOT Overlap

Now let's create a stored procedure which checks for an overlap between a new event's start and end dates with existing events using the CheckEventOverlap function we just created earlier. If there's no overlap, it inserts the new event into the 'Event' table and prints a success message. If there's an overlap, it prints a message indicating the overlap.

Stored procedure to insert event if no date ranges overlap detected

CREATE PROCEDURE InsertEventIfNoOverlap
    @NewStartDate DATETIME,
    @NewEndDate DATETIME
AS
BEGIN
    IF dbo.CheckEventOverlap(@NewStartDate, @NewEndDate) = 0
    BEGIN
        INSERT INTO Event (EventID, StartDate, EndDate)
        VALUES (2, @NewStartDate, @NewEndDate);
        PRINT 'Event inserted successfully.';
    END
    ELSE
    BEGIN
        PRINT 'Event overlaps with existing events.';
    END
END;

Now let's illustrate all the possible scenarios visually below and test each scenario to ensure our CheckEventOverlap function and InsertEventIfNoOverlap stored procedure work correctly.

No Overlap: Event 1 Occurred AFTER Event 2

No Overlap: Event 1 occurred AFTER Event 2

/* Test Case 1 - No Overlap - Event 1 occurred AFTER Event 2 */

-- Make sure Event table is empty prior to testing
TRUNCATE TABLE Event;

-- Insert Event 1
INSERT INTO Event (StartDate, EndDate)
VALUES
    ('2023-01-01 09:00:00', '2023-01-01 10:00:00');

-- Insert Event 2
DECLARE @NewStartDate DATETIME = '2023-01-01 07:00:00';
DECLARE @NewEndDate DATETIME = '2023-01-01 08:59:00';
EXEC dbo.InsertEventIfNoOverlap @NewStartDate, @NewEndDate;

-- Display events
SELECT *
FROM Event;

Event inserted successfully.

EventID	StartDate	EndDate
1	2023-01-01 09:00:00.000	2023-01-01 10:00:00.000
2	2023-01-01 07:00:00.000	2023-01-01 08:59:00.000

No Overlap: Event 1 Occurred BEFORE Event 2

No Overlap: Event 1 occurred BEFORE Event 2

/* Test Case 2 - No Overlap - Event 2 occurred AFTER Event 1 */

-- Make sure Event table is empty prior to testing
TRUNCATE TABLE Event;

-- Insert Event 1
INSERT INTO Event (StartDate, EndDate)
VALUES
    ('2023-01-01 09:00:00', '2023-01-01 10:00:00');

-- Insert Event 2
DECLARE @NewStartDate DATETIME = '2023-01-01 10:01:00';
DECLARE @NewEndDate DATETIME = '2023-01-01 11:00:00';
EXEC dbo.InsertEventIfNoOverlap @NewStartDate, @NewEndDate;

-- Display events
SELECT *
FROM Event;

Event inserted successfully.

EventID	StartDate	EndDate
1	2023-01-01 09:00:00.000	2023-01-01 10:00:00.000
3	2023-01-01 10:01:00.000	2023-01-01 11:00:00.000

Partial Overlap: Event 1 Start Date is between Event 2 Start Date and End Date

Partial Overlap: Event 1 Start Date is between Event 2 Start Date and End Date

/* Test Case 3 - Partial Overlap - Event 1 Start Date is between Event 2 Start Date and End Date */

-- Make sure Event table is empty prior to testing
TRUNCATE TABLE Event;

-- Insert Event 1
INSERT INTO Event (StartDate, EndDate)
VALUES
    ('2023-01-01 09:00:00', '2023-01-01 10:00:00');

-- Insert Event 2
DECLARE @NewStartDate DATETIME = '2023-01-01 08:30:00';
DECLARE @NewEndDate DATETIME = '2023-01-01 09:30:00';
EXEC dbo.InsertEventIfNoOverlap @NewStartDate, @NewEndDate;

Event overlaps with existing events.

Partial Overlap: Event 1 End Date is between Event 2 Start Date and End Date

Partial Overlap: Event 1 End Date is between Event 2 Start Date and End Date

/* Test Case 4 - Partial Overlap - Event 1 End Date is between Event 2 Start Date and End Date */

-- Make sure Event table is empty prior to testing
TRUNCATE TABLE Event;

-- Insert Event 1
INSERT INTO Event (StartDate, EndDate)
VALUES
    ('2023-01-01 09:00:00', '2023-01-01 10:00:00');

-- Insert Event 2
DECLARE @NewStartDate DATETIME = '2023-01-01 09:30:00';
DECLARE @NewEndDate DATETIME = '2023-01-01 10:30:00';
EXEC dbo.InsertEventIfNoOverlap @NewStartDate, @NewEndDate;

Event overlaps with existing events.

Complete Overlap: Event 1 and Event 2 Have the Same Start Date and End Date

Complete Overlap: Event 1 and Event 2 Have the Same Start Date and End Date

/* Test Case 5 - Complete Overlap - Event 1 and Event 2 Have the Same Start Date and End Date */

-- Make sure Event table is empty prior to testing
TRUNCATE TABLE Event;

-- Insert Event 1
INSERT INTO Event (StartDate, EndDate)
VALUES
    ('2023-01-01 09:00:00', '2023-01-01 10:00:00');

-- Insert Event 2
DECLARE @NewStartDate DATETIME = '2023-01-01 09:00:00';
DECLARE @NewEndDate DATETIME = '2023-01-01 10:00:00';
EXEC dbo.InsertEventIfNoOverlap @NewStartDate, @NewEndDate;

Event overlaps with existing events.

Event 1 Time Intervals Covers Event 2 Time Intervals

Event 1 Time Intervals Covers Event 2 Time Intervals

/* Test Case 6 - Event 1 Time Intervals Covers Event 2 Time Intervals */

-- Make sure Event table is empty prior to testing
TRUNCATE TABLE Event;

-- Insert Event 1
INSERT INTO Event (StartDate, EndDate)
VALUES
    ('2023-01-01 09:00:00', '2023-01-01 10:00:00');

-- Insert Event 2
DECLARE @NewStartDate DATETIME = '2023-01-01 09:15:00';
DECLARE @NewEndDate DATETIME = '2023-01-01 09:45:00';
EXEC dbo.InsertEventIfNoOverlap @NewStartDate, @NewEndDate;

Event overlaps with existing events.

Event 2 Time Intervals Covers Event 1 Time Intervals

Event 2 Time Intervals Covers Event 1 Time Intervals

/* Test Case 7 - Event 2 Time Intervals Covers Event 1 Time Intervals */

-- Make sure Event table is empty prior to testing
TRUNCATE TABLE Event;

-- Insert Event 1
INSERT INTO Event (StartDate, EndDate)
VALUES
    ('2023-01-01 09:00:00', '2023-01-01 10:00:00');

-- Insert Event 2
DECLARE @NewStartDate DATETIME = '2023-01-01 08:00:00';
DECLARE @NewEndDate DATETIME = '2023-01-01 11:00:00';
EXEC dbo.InsertEventIfNoOverlap @NewStartDate, @NewEndDate;

Event overlaps with existing events.

Further Reading

You can check out T-SQL How to Check Overlapping Date Ranges: User Subscription Example. The post applies the technique we discussed here.