SQL Server – Constraint to Prevent Overlapping Time Periods

constraintsql servertime

I have a pretty simple table that stores a StartTime and an EndTime, both with the data type of Time(0). I need to enforce a constraint that prevents any overlapping. 'Touching' time periods are OK, like 2pm-3pm and 3pm-4pm. I have created a function to use for the constraint but I am having a hard time dealing with time periods that span the midnight hour. Any help would be appreciated!

SQL Fiddle

This is driving which hours that a product is available every day. So unfortunately I can not attach an exact date to these time periods.

If the product is always available, the StartTime and EndTime would be the same (i.e. 00:00:00 and 00:00:00). I know this is probably bad design.

The active periods are just for a 24 hour window.

The requirement for EndTime to be greater than StartTime was removed (phew!). I have accepted the answer below, thank you!

Best Answer

With the 24 hour restriction you can change your function to the following:

CREATE FUNCTION [dbo].[fnIsItemGroupAvailabilityValid]
(
    @availabilityStart time(0),
    @availabilityEnd time(0)
)
RETURNS int
AS
BEGIN
    DECLARE @OverLappingRows int

    IF(@availabilityEnd > @availabilityStart)
    BEGIN
        SELECT @OverLappingRows = count(*) 
        FROM [dbo].[ItemGroupAvailability] iga
        WHERE @availabilityStart < iga.AvailabilityEnd 
              AND @availabilityEnd > iga.AvailabilityStart                
    END
    ELSE
        SET @OverLappingRows = 1

    RETURN @OverLappingRows
END

This will insure that the times are sequential, if not it returns an @OverLappingRows = 1 as a sentinel value to tell you something went wrong. You can set it to something else like -1 if you want to specifically catch that failure case.

I removed the equals from the comparisons to drop the inclusion aspect when comparing, it should now allow time spans to butt up against each other but not overlap. Dropped the OR as it's redundant now that we're verifying the the two time stamps are sequential before running the query.

Finally, all day should be 0:00 to 23:59, not 0:00 to 0:00 otherwise you need to do a bunch of convoluted logic.

Related Question