Unique date range fields in SQL Server 2008

Question

I have a table that consists of, among other things, two fields named StartTime and EndTime. Both are TIME fields.

I want to add a constraint preventing the insertion of any records that overlap with preexisting time ranges. E.g. if a record already exists with StartTime = 5:00, EndTime = 10:00, I would want an insert with StartTime = 6:00, EndTime = 9:00 to fail due to the overlap.

Is there any way to accomplish this, with or without triggers?

Answer 1

I haven't tried it but I imagine something like this would work:

create trigger preventOverlaps
on infotable
FOR Insert, Update
As
DECLARE @Count int;
select @Count = count(*) from infotable where 
  (inserted.startdate > startDate && inserted.startdate < endDate) ||
  (inserted.endDate < endDate && inserted.endDate > startDate)
if(@Count > 0)
begin
   rollback transaction;
end

Answer 2

The trigger below should work - it is also possible to do this with check contraints, but the logic shown in this post kind of hurts my head.

CREATE TRIGGER [dbo].[DateRangeTrigger]
   ON  [dbo].[TargetTable]
   FOR INSERT, UPDATE
AS 
BEGIN

IF EXISTS (SELECT t.starttime, t.endtime FROM TargetTable t
     Join inserted i
     On (i.starttime > t.starttime AND i.starttime < t.endtime AND i.UniqueId <> t.UniqueId) 
           OR (i.endtime < t.endtime AND i.endtime > t.starttime AND i.UniqueId <> t.UniqueId)
           OR (i.starttime < t.starttime AND i.endtime > t.endtime AND i.UniqueId <> t.UniqueId)
        )
BEGIN
    RAISERROR ('Inserted date was within invalid range', 16, 1)
    IF (@@TRANCOUNT>0)
        ROLLBACK
END


END

Answer 3

This trigger will also work for situations where one timespan completely contains the other. For example, if there's an existing record for 6:00 - 9:00 and you try to insert one for 5:00 - 10:00.
(Based on David Hall's answer)

CREATE TRIGGER DateRangeOverlapTrigger
ON  TargetTable
FOR INSERT, UPDATE
AS 
BEGIN
IF EXISTS
    (SELECT t.UniqueId
    FROM TargetTable t
        JOIN inserted i ON i.starttime < t.endtime
            AND i.endtime > t.starttime
            AND i.UniqueId <> t.UniqueId)
BEGIN
    RAISERROR ('Invalid due to time overlap', 16, 1)
    IF (@@TRANCOUNT > 0)
        ROLLBACK
END
END