Comparing two time fields in database with 2 given time fields
Actually, I think you're wrong - there are conflicts for CM-01 and CM-03, based on the query and the data shown.

Your data shows bookings for:


CM-01 on 2017-07-15 starting at 10:30 and ending at 11:00
CM-03 on 2017-07-15 starting at 10:35 and ending at 11:05


(No AM/PM shown, so assuming all times are military (00:00 to 23:59).

You're checking if resources are free between 11:00 and 12:00 on 2017-07-15.

So, CM-03 should trivially be unavailable, as there's a five minute period where the current booking overlaps with the desired (11 - 12) new booking.

Based on the intent of the rules you've laid out, I'd say CM-01 should also be unavailable. Your comparing the starts and ends of the two times inclusively. That would imply that, if something's booked for 10:30 to 11:00, and is wanted for 11:00 to 12:00, then it shouldn't be available, because the end of the existing booking is the same as the start of the new booking. If you want to allow back-to-back booking, you need to tweak the logic.

However, if we look at the query, I do think there is a problem. Let's say there was a booking for CM-02 on 2017-07-15 from 9:00 to 10:00 (with a NULL Status). That should clearly not prevent a new booking from 11:00 to 12:00 from being added.

However, when we look at your query to find IDs that should be ignored (reformatted for readability):

SELECT ResourceID
  FROM Bookings
 WHERE DATE(StartDateTime) = '2017-07-15'
   AND Status IS NULL
   AND (    (   TIME('11:00:00') &gt;= TIME(StartDateTime)
             OR TIME('11:00:00') &lt;= TIME(EndDateTime)
            )
        AND (   TIME('12:00:00') &gt;= TIME(StartDateTime)
             OR TIME('12:00:00') &lt;= TIME(EndDateTime)
            )
       )


and consider our 9-10 booking for CM-02, here's what happens:


DATE(StartDateTime) = '2017-07-15' - This is true.
Status IS NULL - So is this.
Checking the start time for the desired (11-12) booking against the existing (9-10) booking:


TIME('11:00:00') &gt;= TIME(StartDateTime) - "11:00:00" is greater than "9:00:00", so true.
We can skip the second check, as this branch is already true.

Checking the end time for the desired (11-12) booking against the existing (9-10) booking:


TIME('12:00:00') &gt;= TIME(StartDateTime) - "12:00:00" is greater than "9:00:00", so true.
We can skip the second check, as this branch is already true.

So, all is true - and we apparently have a conflict (except we shouldn't).


Feel free to confirm the above by adding such a record to your test data.

I've worked through this before, and there are two things you need to check:


Does time period A start before time period B ends?
Does time period A end after time period B starts?


If both are true, you have an issue.

Note - this does assume your start and end times are in the correct order. If either time period's start time comes before its end time, this won't work.

So, rewrite the query as:

SELECT ResourceID
  FROM Bookings
 WHERE Status IS NULL
   AND CAST('2017-07-15 11:00:00' as datetime) &lt;= EndDateTime
   AND CAST('2017-07-15 12:00:00' as datetime) &gt;= StartDateTime


Let's compare that to the booking I created, and to the bookings you had for the desired 11-12 booking:


CM-02, 2017-07-15 9:00 - 10:00


11:00 (desired) &lt;= 10:00 (existing) = FALSE
12:00 (desired) >= 9:00 (existing) = TRUE
Result - FALSE, so OK to list for desired booking.

CM-03, 2017-07-15 10:35 - 11:05


11:00 (desired) &lt;= 11:05 (existing) = TRUE
12:00 (desired) >= 10:35 (existing) = TRUE
Result - TRUE, so not available for desired booking.

CM-01, 2017-07-15 10:30 - 11:00


11:00 (desired) &lt;= 11:00 (existing) = TRUE
12:00 (desired) >= 10:30 (existing) = TRUE
Result - TRUE, so not available for desired booking.



If you want to allow back-to-back bookings (like for CM-01, 10:30 - 11:00 and 11:00 - 12:00), then change the two comparisons to be &lt; and &gt;, not &lt;= and &gt;=

With that change:


CM-01, 2017-07-15 10:30 - 11:00


11:00 (desired) &lt; 11:00 (existing) = FALSE
12:00 (desired) > 10:30 (existing) = TRUE
Result - FALSE, so OK to list for desired booking.



NOTE: Code is untested.