How can I determine in SQL Server if a dateTime range overlaps another
I am trying to determine if a given start time and end time conflicts with another record. I am currently using the following query and if I get 1 or more results I know it has conflicted:
AND @StartTime BETWEEN Sheet.StartTime AND Sheet.EndTime
OR @EndTime BETWEEN Sheet.StartTime AND Sheet.EndTime
This works well but really a user should be able to enter the same start time as a previous end time, ie. last jobs endtime was at 6am, new jobs start time was at 6am. The above query does not allow for this situation.
I have a feeling I may need to use greater than and less than rather than between but would be interested to hear any suggestions.
Thanks in advance
Using "greater(less) than" is better for performance so Microsoft recommends this clause. You should use it.
As you already figured out, the
BETWEEN operator is inclusive (see documentation here).
I think you need to do the following:
SELECT Sheet.SheetID, Sheet.StartTime, Sheet.EndTime, FROM Sheet WHERE (@StartTime > Sheet.StartTime AND @StartTime < Sheet.EndTime) OR (@EndTime > Sheet.StartTime AND @EndTime < Sheet.EndTime)
Note that I didn't put in >= or <= operators, as this would prevent the 6am end/6am start situation you want to allow.
Edit Just had a thought - what if the new Sheet doesn't start or end within the bounds of an existing Sheet, but there is an entire other sheet contained within it? Can that happen? For example, you have one existing Sheet record which starts at 2am and ends at 4am. You try to insert a new one which starts at 1am and ends at 5am. Your query (and my original one) would allow this!
You'd have to amend it as follows:
SELECT Sheet.SheetID, Sheet.StartTime, Sheet.EndTime, FROM Sheet WHERE (@StartTime > Sheet.StartTime AND @StartTime < Sheet.EndTime) OR (@EndTime > Sheet.StartTime AND @EndTime < Sheet.EndTime) OR (@StartTime < Sheet.StartTime AND @EndTime > Sheet.EndTime)
Define 'conflicts with'...but if you mean 'overlaps', then the condition should be:
WHERE @StartTime < Sheet.EndTime AND @EndTime > Sheet.StartTime
You can choose '
<=' or '
>=' instead of '
<' or '
>' if you want exactly the same instant to count.
The condition checks that the value in
@StartTime comes before the entry in Sheet ended, and that the value in
@EndTime occurred after the entry in Sheet started. If both conditions are true, the entry in Sheet overlaps with