SQL Server 2019 (Linux)

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
CREATE TABLE Event (
    EventID INT IDENTITY(1, 1) PRIMARY KEY,
    StartDate DATETIME,
    EndDate DATETIME
);
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;
CREATE PROCEDURE InsertEventIfNoOverlap
    @NewStartDate DATETIME,
    @NewEndDate DATETIME
AS
BEGIN
    IF dbo.CheckEventOverlap(@NewStartDate, @NewEndDate) = 0
    BEGIN
        INSERT INTO Event (StartDate, EndDate)
        VALUES (@NewStartDate, @NewEndDate);
        PRINT 'Event inserted successfully.';
    END
    ELSE
    BEGIN
        PRINT 'Event overlaps with existing events.';
    END
END;
/* 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;


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


Event inserted successfully.


/* 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;


EventID
StartDate
EndDate


1
2023-01-01 09:00:00.000
2023-01-01 10:00:00.000

2
2023-01-01 10:01:00.000
2023-01-01 11:00:00.000


Event inserted successfully.


/* 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.
1 rows affected

/* 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.
1 rows affected

/* 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.
1 rows affected

/* 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.
1 rows affected

/* 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.
1 rows affected
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
EventID	StartDate	EndDate
1	2023-01-01 09:00:00.000	2023-01-01 10:00:00.000
2	2023-01-01 10:01:00.000	2023-01-01 11:00:00.000