Search code examples
sqltimeoverlap

SQL Time Overlap/Conflict Query

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.

Solution

  • 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