Between the time in SQL Server and ASP.NET

I am storing STARTTIME and ENDTIME in a SQL Server table by using ASP.NET webform.

 timeTableID       startTimeVal      endTimeVal
-------------------------------------------------
    1       07:30:00.0000000    08:00:00.0000000
    2       08:00:00.0000000    08:30:00.0000000
    3       08:15:00.0000000    08:25:00.0000000

Now, as you can see in the row with timeTableID = 2, the inserted time value is 8:00 & 8:30 AM,

But in the row with timeTableID = 3, the inserted time value is 8:15 & 8:25 AM,

I don’t want this because its within value of row 2.

My problem is, the next entry should not between the already inserted time.

My attempt:

select timeTableID 
from timetableTbl 
where dayVal = 'MONDAY' 
  and StartTimeVal between '" + startTimeTxt.Text + "' and '" + endTimeTxt.Text + "' and endTimeVal between '" + startTimeTxt.Text + "' and '" + endTimeTxt.Text + "'

Here startTimeTxt is the textbox for startTimeVal and endTimeTxt is the textbox for endTimeVal.

Please help me with this

Answer

It sounds like you are trying to detect and avoid overlapping time intervals prior to insert. The way to test for this is not obvious, but the following works: Given two time intervals [start1, end1) and [start2, end2), they overlap iff (start1 < end2 AND start2 < end1).

Note that this specifically allows for adjacent intervals where start1 = end2 or start2 = end1, as is common for continuous ranges and reflects the condition for records 1 and 2 above. (If we were dealing with discrete values and inclusive ranges, the tests would use <= instead of <.)

This also assumes that you want to avoid any overlap, not just the case where the new interval is entirely contained within an existing interval. For example, given records 1 & 2 above, you would like to also prohibit intervals 7:15am-7:45am (overlaps start of record 1), 8:15am-8:45am (overlaps end of record 2), and 6:00am-12:00pm (completely overlaps both).

In this case, the check for a conflicting interval would be something like

select timeTableID 
from timetableTbl 
where dayVal = @day
and StartTimeVal < @endTime
and EndTimeVal > @startTime  -- Same as @startTime < EndTimeVal

Where @day, @startTime, and @endTime are parameters derived from validated user input.