CODEWITHSUNDEEP
mysqldate
AymAn AbuOmar
AymAn AbuOmar

Reputation: 423

Check If A Particular Date Is Not Overlapping Any Range In A Table

I am creating a hotel booking application, and I want the user to check if a particular date range is available or not, for example :

BookingID | StartingDate | EndDate
    1     | 01/01/2014   | 10/01/2014
    2     | 15/01/2014   | 20/01/2014

Suppose these are the records of my booking table. Now, the user wants to check this date range:

(Supposing there is one room in the hotel)

StartingDate = 11/01/2014
EndDate      = 19/01/2014

As you can see, this date range is not available starting from 15/01 until 20/01.

How do I check to see if a date range is not overlapping with any date ranges in the existing records?

Upvotes: 0

Views: 139

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1269513

You can use this logic:

select count(*) as NumOverlappingRecords
from booking b
where @StartingDate <= b.EndDate and
      @EndDate >= b.startingDate;

You want NumOverlappingRecords to be 0 for no conflicts.

Upvotes: 1

Willz
Willz

Reputation: 78

Simple query:

SELECT COUNT(BookingID) 
FROM foo f 
WHERE f.StartingDate < to_date('19/01/2014') AND f.EndDate > to_date('11/01/2014');

Upvotes: 2

Related Questions