How to Prevent Duplicate Records with Date Range Overlaps in Salesforce Apex

Keeping your Salesforce data clean means enforcing business rules that go way beyond simple unique fields. One of the most common real-world challenges developers face is preventing users from creating duplicate records with conflicting timeframes. Whether you are building a system for equipment reservations, employee PTO requests, or hotel bookings, you cannot afford to have two active records occupying the same exact dates.

In plain words: To stop date overlaps, we use an Apex trigger that fires right before a user saves a record. The code instantly scans the database for any existing records with the same parent ID. If it finds that the new start and end dates cross paths with an already booked timeframe, it blocks the save and displays a friendly error message on the screen.

Key Points Summary

• Standard Validation Rules cannot query other records; you must use Apex (or complex Flow loops) to validate against existing database rows.
• Date overlap math is incredibly simple once you know the formula: StartA <= EndB AND EndA >= StartB.
• Modern Salesforce development demands that you keep logic out of the .trigger file and move it into an Apex Handler class.
• Your code must account for updates. If a user is just changing a picklist value on an existing booking, the record shouldn't trigger an overlap error against itself!

The Golden Formula for Date Overlaps

Trying to map out every possible way two dates can overlap (e.g., starting before and ending during, starting during and ending after) will leave you with a massive, unreadable chunk of code. Fortunately, mathematics gives us a shortcut.

Two date ranges, Range A (StartA to EndA) and Range B (StartB to EndB), overlap if and only if:

StartA <= EndB AND EndA >= StartB

By translating this exact logic into your Apex code, you cover every single overlapping scenario perfectly with just one line of boolean math.

Modern Apex Implementation

Writing logic directly inside a Trigger is considered bad practice today. Instead, we use a Trigger Handler. Below is a fully bulkified, modern solution for a custom object called Booking__c.

Real-Life Example: Preventing Overlapping Booking Dates
First, we create a clean, logicless trigger that routes to our handler class on before insert and before update events.

trigger BookingTrigger on Booking__c (before insert, before update) {
    if (Trigger.isBefore) {
        BookingTriggerHandler.preventDoubleBooking(Trigger.new, Trigger.isUpdate);
    }
}

Next, we build the actual Handler class to process the heavy lifting, query the database safely, and attach the error.

public class BookingTriggerHandler {
    
    public static void preventDoubleBooking(List<Booking__c> newBookings, Boolean isUpdate) {
        Set<Id> resourceIds = new Set<Id>();
        
        // Step 1: Collect parent Resource IDs to keep our SOQL query highly targeted
        for (Booking__c b : newBookings) {
            if (b.Resource__c != null && b.Start_Date__c != null && b.End_Date__c != null) {
                resourceIds.add(b.Resource__c);
            }
        }
        
        if (resourceIds.isEmpty()) return;
        
        // Step 2: Query all existing bookings for those specific resources
        List<Booking__c> existingBookings = [
            SELECT Id, Resource__c, Start_Date__c, End_Date__c 
            FROM Booking__c 
            WHERE Resource__c IN :resourceIds
        ];

        // Step 3: Compare incoming records against existing ones in memory
        for (Booking__c newB : newBookings) {
            for (Booking__c existingB : existingBookings) {
                
                // CRITICAL: Skip comparing the record to itself during an update
                if (isUpdate && newB.Id == existingB.Id) {
                    continue;
                }

                // If they belong to the same resource, check for a date collision
                if (newB.Resource__c == existingB.Resource__c) {
                    Boolean isOverlapping = (newB.Start_Date__c <= existingB.End_Date__c) && 
                                            (newB.End_Date__c >= existingB.Start_Date__c);

                    if (isOverlapping) {
                        newB.addError('Conflict! A booking already exists from ' + 
                            existingB.Start_Date__c.format() + ' to ' + existingB.End_Date__c.format());
                        break; // Stop checking further once a duplicate is found
                    }
                }
            }
        }
    }
}

Common Developer Mistakes to Avoid:

• SOQL Queries Inside Loops: Never write a SELECT statement inside a for loop. Our code avoids this by collecting all IDs first, running one query, and comparing the lists in memory.
• Forgetting the Self-Check on Updates: If you forget to bypass newB.Id == existingB.Id, updating a record will cause it to crash because it thinks it is overlapping with its own original database row.
• Ignoring Null Dates: Always verify that start and end dates actually contain data before doing comparisons, otherwise you will hit a NullPointerException.

Frequently Asked Questions

Can I achieve this using Salesforce Record-Triggered Flows instead of Apex?

Yes, but with caveats. You can use a Before-Save Flow that runs a "Get Records" element to look for overlapping dates. However, Flows handle bulk data loads (like Data Loader) less efficiently than Apex. For complex, high-volume objects, Apex remains the most scalable and robust choice.

What if I only want to check overlap for Business Days?

The standard overlap math assumes calendar days. If you only care about business days, you will need to utilize a custom Apex utility method to calculate working days (often utilizing the standard Salesforce BusinessHours class) before comparing the overlap.

How do I handle DateTime fields instead of plain Date fields?

The core logic (StartA <= EndB AND EndA >= StartB) works perfectly for DateTime fields as well. Just ensure that the fields you are comparing share the exact same data type.

Always write test classes that validate bulk operations. Insert a list of 200+ records at once in your test method to guarantee your Trigger remains governor limit safe.

360 Summary Card

• Required Events: before insert, before update
• Method to Block Save: SObject.addError()
• Core Logic Formula: NewStart <= ExistingEnd AND NewEnd >= ExistingStart
• Architecture Standard: Use a Trigger Handler class to keep your code testable, modular, and easy to maintain.