how to select a date if it is already between two values sql server

Question

I am trying to select between 2 different dates in 2 different columns and I thought I understood how to until I reached this issue. I think I am better providing an example:

A room has an IN date and an OUT date (hotel issues related). I want to select rooms NOT between inDate and outDate. Therefore I could use this query:

select * from room where  inDate and outDate not between '2019-06-19' and '2019-06-26';

Let us imagine that we have a room with inDate = '2019-06-18' and outDate = '2019-06-21'.

My question is, which could be the right query to know that an user can not book that room because at that time the room is unavailable? Because if we use the previously mentioned query, that room will be available, and it is obviously not.

I have seen many pages on this page asking similar things, but I have tried not to create a duplicate. Sorry in advance if this is a duplicate and I hope I explained myself correctly.

Possible duplicate of [Determine Whether Two Date Ranges Overlap](https://stackoverflow.com/questions/325933/determine-whether-two-date-ranges-overlap) — EzLo, Jun 19 '19 at 10:41

score 2 · Accepted Answer · answered Jun 19 '19 at 00:29

If you are looking for available rooms, then the logic looks like this:

select *
from room r
where not (inDate <= '2019-06-26' and
           outDate >= '2019-06-19'
          );

Two time periods (such as a room reservation and an interval) overlap when each starts before the other ends. The not is because you want rooms with no reservations.

In actual fact, the above doesn't look quite right. You should have one table for rooms and one for reservations. Then the query would look like:

select r.*
from rooms r
where not exists (select 1
                  from reservations re
                  where re.room_id = r.room_id and
                        re.inDate <= '2019-06-26' and
                        re.outDate >= '2019-06-19'
                 );

actually my real query looks similar to yours: SELECT distinct room.* FROM room, booking where room.id not in (select idroom from booking where inDate and outDate NOT between '2019-06-19' and '2019-06-20') order by room.price Desc; but it seems I thought that i could just use a between clause to pick them all. Will give it some try and thought and I will answer you back with the results. Up to what I have tried solves the issue but I want to ensure it. Many many thanks! — José María De Juan Vázquez, Jun 19 '19 at 00:37

how to select a date if it is already between two values sql server

1 Answers1