SQL Server 2017

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

CREATE TABLE Bookings (Id INT, RoomId INT, ArrivalDate Date, DepartureDate DATE)
INSERT INTO Bookings VALUES
(1, 1, '2018-09-17', '2018-09-18'),
(2, 1, '2018-09-18', '2018-09-20'),
(3, 1, '2018-09-21', '2018-09-25');
3 rows affected

SELECT *
FROM Bookings
WHERE '2018-09-23' > ArrivalDate AND DepartureDate > '2018-09-20'


Id
RoomId
ArrivalDate
DepartureDate


3
1
21/09/2018 00:00:00
25/09/2018 00:00:00


SELECT *
FROM Bookings
WHERE '2018-09-27' > ArrivalDate AND DepartureDate > '2018-09-20'


Id
RoomId
ArrivalDate
DepartureDate


3
1
21/09/2018 00:00:00
25/09/2018 00:00:00


SELECT *
FROM Bookings
WHERE '2018-09-21' > ArrivalDate AND DepartureDate > '2018-09-20'


Id
RoomId
ArrivalDate
DepartureDate