Preventing Overlapping Date Ranges in a Table

Two active prices for the same date leave the report to choose a winner. Preventing overlapping date ranges starts with a clear endpoint rule and a check that handles several changed rows. It also needs locking, because two concurrent writers can both miss each other.

Choose Half-Open or Closed Ranges

The examples use half-open ranges: StartDate is included and EndDate is excluded. An interval from Monday to Tuesday covers Monday, then the next interval can start Tuesday without overlap. Two ranges overlap when the first start is before the second end and the first end is after the second start. Equality at the boundary is allowed.

Closed ranges include both ends. They need less than or equal and greater than or equal in the overlap test, so touching endpoints overlap. Choose one contract and use it everywhere. I ask about midnight boundaries before discussing triggers. Which price applies exactly at the change instant? The application, reports, and stored rule must all give the same answer.

Create Valid Individual Ranges

An ordinary CHECK constraint can ensure each range has a start before its end. It cannot compare that row with every other row for the same resource. A unique start also cannot prevent a different start from overlapping the same period. Use a separate cross row rule. The temporary sample intentionally contains overlaps for the detection queries.

CREATE TABLE #Ranges
(
    RangeID int NOT NULL PRIMARY KEY,ResourceID int NOT NULL,
    StartDate date NOT NULL,EndDate date NOT NULL,
    CHECK(StartDate<EndDate)
);
INSERT #Ranges VALUES
    (1,10,'20260101','20261201'),(2,10,'20260201','20260301'),
    (3,10,'20260401','20260501'),(4,20,'20260101','20260201'),
    (5,20,'20260201','20260301');

Run the temporary examples in one session, because the temporary table disappears when the session ends. Keep resource identity in every comparison so unrelated customers or products do not conflict. The end dates are mandatory in this contract. Open ended ranges need a deliberate comparison rule rather than letting NULL turn an overlap predicate into unknown.

Find Overlapping Date Ranges With a Self-Join

Join each range to later range identifiers for the same resource. The identifier inequality avoids comparing a row with itself and avoids returning each pair twice. Apply both interval predicates. This finds partial overlaps and nested ranges. Inspect the names and endpoints together before deciding which record to repair or retire.

SELECT a.ResourceID,a.RangeID AS FirstRange,b.RangeID AS SecondRange,
       a.StartDate AS FirstStart,a.EndDate AS FirstEnd,
       b.StartDate AS SecondStart,b.EndDate AS SecondEnd
FROM #Ranges AS a
JOIN #Ranges AS b ON b.ResourceID=a.ResourceID AND b.RangeID>a.RangeID
WHERE a.StartDate<b.EndDate AND a.EndDate>b.StartDate
ORDER BY a.ResourceID,a.RangeID,b.RangeID;

I preserve the source rows before fixing existing overlaps. Picking the latest inserted row is not always the business rule. A late entry can correct an earlier price or simply repeat it incorrectly. Ask the owner to identify the authoritative period, then use a reviewed repair. Detection SQL establishes a conflict, not which value should survive.

Catch Nested Overlapping Date Ranges That LAG Misses

LAG returns the preceding end date when ordered by start. It detects overlaps with that preceding row. Nested intervals expose its limitation: a short inner range can end before the next range while an earlier outer range still covers both. Use the maximum of all earlier ends to detect that broader conflict, and retain LAG to explain the adjacent relationship.

WITH ordered AS
(
    SELECT *,
           LAG(EndDate) OVER
               (PARTITION BY ResourceID ORDER BY StartDate,RangeID) AS PreviousEnd,
           MAX(EndDate) OVER
               (PARTITION BY ResourceID ORDER BY StartDate,RangeID
                ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING) AS GreatestEarlierEnd
    FROM #Ranges
)
SELECT ResourceID,RangeID,StartDate,EndDate,PreviousEnd,GreatestEarlierEnd,
       CASE WHEN StartDate<PreviousEnd THEN 1 ELSE 0 END AS AdjacentOverlap
FROM ordered
WHERE StartDate<GreatestEarlierEnd;

The unique identifier breaks ties in the window order. The running maximum flags a later row overlapping at least one earlier row, but it does not list every conflicting pair. Use the self-join when you need the specific pairs. A compact detection report and a complete repair inventory answer different operational questions.

Block New Overlapping Date Ranges With a Trigger

Create a clean permanent demonstration table in a disposable database. Its index begins with resource and start date for the overlap search. An AFTER trigger compares every inserted or updated row against the final table, excluding the same identifier. Rows inserted together are visible too, so the check handles a multirow statement rather than assuming one value.

CREATE TABLE dbo.PriceRange
(
    RangeID int NOT NULL CONSTRAINT PK_PriceRange PRIMARY KEY,
    ResourceID int NOT NULL,StartDate date NOT NULL,EndDate date NOT NULL,
    PriceCents bigint NOT NULL,
    CONSTRAINT CK_PriceRange_Dates CHECK(StartDate<EndDate)
);
CREATE INDEX IX_PriceRange_Resource_Start
ON dbo.PriceRange(ResourceID,StartDate) INCLUDE(EndDate);
GO
CREATE TRIGGER dbo.PriceRange_NoOverlap
ON dbo.PriceRange
AFTER INSERT,UPDATE
AS
BEGIN
    SET NOCOUNT ON;
    IF EXISTS
    (
        SELECT 1
        FROM inserted AS i
        JOIN dbo.PriceRange AS r WITH
            (UPDLOCK,HOLDLOCK,INDEX(IX_PriceRange_Resource_Start))
          ON r.ResourceID=i.ResourceID AND r.RangeID<>i.RangeID
         AND r.StartDate<i.EndDate AND r.EndDate>i.StartDate
    )
        THROW 50001,'Overlapping ranges are not allowed for this resource.',1;
END;
GO

CREATE TRIGGER has its own batch. The trigger runs inside the modifying transaction. UPDLOCK and HOLDLOCK request locking reads and serializable protection for the checked search, rather than a versioned read that misses another writer. The index supports that search. Keep the whole write operation short, because those locks remain until the transaction ends.

Test Accepted and Rejected Statements

Adjacent half-open periods should be accepted. A period crossing their boundary should be rejected. The second test inserts two conflicting new rows in the same statement. TRY CATCH prints the error for inspection. Execute the tests in a rehearsal database, then confirm rejected statements leave no partial rows behind.

INSERT dbo.PriceRange VALUES
    (1,10,'20260101','20260201',1000),(2,10,'20260201','20260301',1200);
BEGIN TRY
    INSERT dbo.PriceRange VALUES(3,10,'20260115','20260215',1100);
END TRY
BEGIN CATCH
    SELECT ERROR_NUMBER() AS ErrorNumber,ERROR_MESSAGE() AS ErrorMessage;
END CATCH;
BEGIN TRY
    INSERT dbo.PriceRange VALUES
        (4,20,'20260101','20260301',1000),(5,20,'20260201','20260401',1200);
END TRY
BEGIN CATCH
    SELECT ERROR_NUMBER() AS ErrorNumber,ERROR_MESSAGE() AS ErrorMessage;
END CATCH;
SELECT RangeID,ResourceID,StartDate,EndDate FROM dbo.PriceRange;

Also test updates changing dates or resource identity. A bulk write and an application update can reach the same trigger through different paths. Review trigger disabling, bulk load options, and privileged maintenance procedures. A rule bypassed during the largest import is an awkward place to discover that the ordinary screen worked perfectly.

Rehearse Two Concurrent Writers

Run overlapping writes from two connections against the same resource. Verify that both cannot commit conflicting periods. An AFTER trigger sees locks already taken by each write, so contention can produce a deadlock. One transaction must fail and the application must handle that failure. Recheck the rule on any retry rather than assuming the original request remains valid.

I test concurrent writes before calling a range rule complete. A SELECT followed by INSERT under ordinary read committed isolation leaves a race. For a busy workload, consider a controlled procedure that locks a stable parent resource row before modifying ranges. Have every writer follow the same protocol, and retain database enforcement where direct writes remain possible.

Keep the Endpoint Contract in Every Reader

Query a half-open active period with StartDate less than or equal to the requested date and EndDate greater than it. Do not use BETWEEN, which includes both ends. For timestamps, choose precision and time zone rules alongside the interval contract. Avoid subtracting a millisecond from an end to imitate inclusive storage.

Preventing overlapping date ranges requires valid rows, correct comparisons, multirow checks, and coordinated concurrency. Keep an audit query for existing data even after enforcement. Test the access plan as volume grows and revisit locking costs with actual evidence. A clean boundary rule makes both storage and reporting easier to explain.

Related reading on this blog: How to Use Instead of Trigger and Types of Triggers.

A date range rule is not a single comparison, it is a boundary contract enforced across writers.

Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.