MySQL query to get rental orders within a date range-CodePudding

I have ‘rental’ table with the following columns – id, date_from, date_to

I am trying to find if there are any rentals between two dates (to look up available inventory). I tried the following query and it works – SELECT * FROM 'rental' WHERE ('date_from' BETWEEN '2022-06-15' AND '2022-06-17') OR ('date_to' BETWEEN '2022-06-15' AND '2022-06-17');

However, if a rental record exists in database that starts before 2022-06-15 and ends after 2022-06-17, this query does not return that record.

How can I query active rentals within a date range? Thank you

CodePudding user response：

Your query is redundant, as I assume your rental cannot have a date_from after a date_to. So all you'd need to check is if a date_from is prior to your period, and the date_to before. FYI MySQL Dates can be queried using <, > and = operators which are a bit easier to interpret. See:

create table rental (id integer, date_from date, date_to date);
insert into rental (id, date_from, date_to) values (1, '2022-06-13', '2022-06-18');
insert into rental (id, date_from, date_to) values (2, '2022-06-16', '2022-06-17');
insert into rental (id, date_from, date_to) values (3, '2016-09-02', '2018-09-04');


select * from rental where (date_from <= '2022-06-17') and (date_to >= '2022-06-13');

Will return row:

1 2022-06-13T00:00:00.000Z 2022-06-18T00:00:00.000Z

2 2022-06-16T00:00:00.000Z 2022-06-17T00:00:00.000Z