Prevent Duplicate & Overlapping Date Records in Salesforce Using Apex Triggers

In plain words: Checking for duplicate date ranges ensures you don't double-book a room, schedule overlapping shifts, or create conflicting events. Instead of matching exact dates, an Apex trigger checks whether a new record's date window overlaps with any existing booking before saving.

Maintaining high data quality often requires business validations that standard duplicate rules cannot handle. A classic scenario is booking validation: ensuring that an asset, room, or user schedule does not have overlapping start and end dates. By leveraging a bulkified before insert, before update Apex trigger, you can validate date ranges in real time and block conflicting records with custom error messages.

1. Understanding the Date Overlap Formula

Two date ranges (StartA to EndA) and (StartB to EndB) overlap if and only if:

Overlap Condition: StartA ≤ EndB AND EndA ≥ StartB

Checking only for exact start-date or end-date equality is insufficient because a new booking could sit entirely inside an existing range or completely encompass it. Your SOQL query and logic must evaluate the full range.

2. Writing the Bulkified Trigger Handler

To avoid hitting SOQL governor limits, we follow the Trigger Handler pattern: collect the boundary dates from incoming records, run a single targeted SOQL query, and compare records in memory.

Step 1: Create the Handler Class (EventDuplicateChecker.cls)

public with sharing class EventDuplicateChecker {
    public static void checkDateOverlap(List<Event__c> newEvents, Map<Id, Event__c> oldEventMap) {
        Date minFromDate;
        Date maxToDate;
        List<Event__c> validEvents = new List<Event__c>();

        // 1. Basic validation and collecting date boundaries
        for (Event__c evt : newEvents) {
            if (evt.From_Date__c == null || evt.To_Date__c == null) {
                continue;
            }
            if (evt.From_Date__c > evt.To_Date__c) {
                evt.addError('To Date must be equal to or later than From Date.');
                continue;
            }

            validEvents.add(evt);

            if (minFromDate == null || evt.From_Date__c < minFromDate) {
                minFromDate = evt.From_Date__c;
            }
            if (maxToDate == null || evt.To_Date__c > maxToDate) {
                maxToDate = evt.To_Date__c;
            }
        }

        if (validEvents.isEmpty() || minFromDate == null || maxToDate == null) {
            return;
        }

        // 2. Query only existing records that could potentially overlap
        Set<Id> currentIds = oldEventMap != null ? oldEventMap.keySet() : new Set<Id>();
        List<Event__c> existingEvents = [
            SELECT Id, From_Date__c, To_Date__c 
            FROM Event__c 
            WHERE Id NOT IN :currentIds 
              AND From_Date__c <= :maxToDate 
              AND To_Date__c >= :minFromDate
            WITH USER_MODE
        ];

        // 3. In-memory comparison against existing records and other records in the same batch
        for (Integer i = 0; i < validEvents.size(); i++) {
            Event__c currentEvt = validEvents[i];

            // Compare against database records
            for (Event__c existing : existingEvents) {
                if (currentEvt.From_Date__c <= existing.To_Date__c && currentEvt.To_Date__c >= existing.From_Date__c) {
                    currentEvt.addError('This schedule overlaps with an existing booking from ' + 
                        existing.From_Date__c.format() + ' to ' + existing.To_Date__c.format() + '.');
                    break;
                }
            }

            // Compare against sibling records within the same Trigger.new batch
            for (Integer j = i + 1; j < validEvents.size(); j++) {
                Event__c siblingEvt = validEvents[j];
                if (currentEvt.From_Date__c <= siblingEvt.To_Date__c && currentEvt.To_Date__c >= siblingEvt.From_Date__c) {
                    siblingEvt.addError('Conflict detected: Another record in this batch shares an overlapping date range.');
                }
            }
        }
    }
}

Step 2: Create the Apex Trigger (EventTrigger.trigger)

trigger EventTrigger on Event__c (before insert, before update) {
    if (Trigger.isBefore && (Trigger.isInsert || Trigger.isUpdate)) {
        EventDuplicateChecker.checkDateOverlap(Trigger.new, Trigger.oldMap);
    }
}

360 Duplicate Trigger Architecture Card:

• Context: Must run in before insert and before update to block invalid records prior to database commit.
• Query Selectivity: Always bound your SOQL query by minFromDate and maxToDate instead of querying all rows in the object.
• Update Self-Exclusion: Exclude Trigger.oldMap.keySet() in the query so updating a record does not flag itself as a duplicate.
• Batch Self-Comparison: Compare records within Trigger.new against each other to prevent duplicate entries inside a single bulk upload.

3. Critical Traps & Best Practices

Developer Trap: Unfiltered SOQL Queries
Writing SELECT Id, From_Date__c, To_Date__c FROM Event__c without a WHERE clause will quickly hit the 50,000 query row governor limit as your org grows. Always filter the query using the earliest and latest dates present in the active transaction.

• Verify End Date ≥ Start Date: Always enforce that To_Date__c is greater than or equal to From_Date__c before performing range comparisons.
• Include Parent / Resource Dimensions: If bookings belong to specific rooms or assets, add the lookup filter (e.g., WHERE Resource__c IN :resourceIds) to prevent false positives across different resources.
• Enforce Security: Use WITH USER_MODE in queries to respect Object-Level and Field-Level permissions automatically.

Summary

Preventing overlapping dates in Salesforce requires a robust overlap formula, efficient SOQL bounding, and in-memory batch validation. By moving your validation logic into a dedicated Trigger Handler class and excluding active record IDs during updates, you protect data integrity without risking governor limit exceptions.