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
>Solution :
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