Get specific date from two dates in sql

Question

I need to write a query to find out how many hotel bookings have been made during a specific month, let's say January. All rows have a to and from date, both in data type date. I've tried eg. month(dateto) and dateto between 'YYYY-MM-DD' AND 'YYYY-MM-DD', but if there was a booking from 2019-12-30 to 2020-02-01 my query won't find this. Any suggestion to how to solve this?

Answer 1

Can you show any attempt from you?

Answer 2

Attempt 1:
SELECT *
FROM booking
WHERE datefrom BETWEEN '2021-01-01' AND '2021-01-31' OR
dateTo BETWEEN '2021-01-01' AND '2021-01-31';

Attempt 2:
SELECT *
FROM booking
WHERE MONTH(dateFrom) = 1 OR MONTH(dateTo) = 1;

but these wont find a booking with:
dateFrom: "2020-12-31"
dateTo: "2021-02-22"

Answer 3

Check this post about overlapping date ranges, it has detailed explanation of the solution.
Also it is useful to add your SQL flavor/dialect in tags, because different database engines may have different approach to comparing dates.

https://stackoverflow.com/questions/325933/determine-whether-two-date-ranges-overlap