SQL Time Overlap/Conflict Query

Question

I have a requirement where I need to assign some resource for some tine frame .For example

Existing in Database : John Smith -- 3/1/2011 -- 6:00 AM To 7:00 AM -- Economics
To Be Inserted: John Smith -- 3/1/2011 -- 6:30 AM to 7:00 AM -- Maths

Here while inserting second row I want to check this time overlap if its overlapping I want to skip insert.How can I achieve this ?I have Date , FromTime and ToTime in seperate columns.

Same Question: http://stackoverflow.com/questions/781895/checking-for-time-range-overlap-the-watchman-problem-sql — Four, Feb 28 '11 at 14:35

jenson-button-event · Accepted Answer · 2011-02-28T15:05:08.367

use computed dates using a conversion:

cast(cast(mydatecolumn as varchar(20)) + ' ' + 
 cast(mytimecolumn as varchar(20)) as datetime)

yields e.g. start_datetime and end_datetime

assume variables: @inserted_start_datetime, @inserted_end_datetime, @name

Insert Into MyTable(<values>)
Where not exists ( 
 select * from table t2 
 where name = @name 
 and (@inserted_start_datetime between start_datetime and end_datetime 
  or @inserted_end_datetime between start_datetime and end_datetime 
  or start_datetime between @inserted_start_datetime and @inserted_end_datetime 
  or end_datetime between @inserted_start_datetime and @inserted_end_datetime ))

EDITED

score 0 · Answer 2 · answered Feb 28 '11 at 14:37

if your from to dates are timedates in the database you could try something like this

if not exists ( select * from table where name = @name and @startTime between startTime and endTime or @endTime between startTime and endTime ) begin

do insert.

where @name = John Smith @startTime = 3/1/2011 6:30 @endTime = 3/1/2011 7:00

end

score 0 · Answer 3 · answered Feb 28 '11 at 15:03

Basically like this:

INSERT INTO atable (Name, Date, FromTime, ToTime, Subject)
SELECT
  @Name,
  @Date,
  @FromTime,
  @ToTime,
  @Subject
WHERE NOT EXISTS (
  SELECT * FROM atable
  WHERE Name = @Name AND Date = @Date
    AND (FromTime >= @FromTime AND FromTime < @ToTime  OR
         @FromTime >= FromTime AND @FromTime < ToTime)
)

You might want to add some data conversion if and where needed.

SQL Time Overlap/Conflict Query

3 Answers3