SQL Check if a date exists between certain 2 dates-CodePudding

I have these tables

Hotel (hotelNo, hotelName, city)
Room (roomNo, hotelNo, type, price)
Booking (hotelNo, guestNo, dateFrom, dateTo, roomNo)
Guest(guestNo, guestName, guestAddress)

How can I list hotels and rooms that were not reserved on "1-Mar-2022"?

What I've tried so far:

SELECT * 
FROM Hotel h, Room r, Booking b
WHERE h.hotelNo = r.hotelNo 
  AND b.roomNo = r.roomNo
  ??? AND NOT (b.dateFrom and b.dateTo not contains ‘2022-03-01’) ???

CodePudding user response：

You can use '2022-03-01' NOT BETWEEN b.dateFrom and b.dateTo

CodePudding user response：

SELECT * FROM Hotel h,
JOIN  Room r  ON h.HotelNo=r.HotelNo
JOIN Booking b ON b.roomNo = r.roomNo
 WHERE '2022-03-01' NOT BETWEEN b.dateFrom and b.dateTo