How to select dates where some dates of range are in given range?-CodePudding

My table presences holds time ranges, where an employee is present. The range has a start date and end date.

I know how to select a date being in a date range, by simply using BETWEEN but, what I want to do, is to provide a given date range, e.g. 2022-04-04 to 2022-04-06 and I want to get all rows where an employee as available at at least one day of the given range.

When the presences table has a row with start 2022-01-01 and end 2022-12-31, what means, the employee is present every day, then the row must be returned, because the provided range is in the presence range.

Another example, an employee is present on 2022-04-05 only, then this row must be returned as well, as the employee is present at least once in the provide range.

Here is a fiddle from what I got so far: http://sqlfiddle.com/#!9/2b3506/4

CodePudding user response：

You can compare the date value of start to the end date and the date value of end to the start date of the queried date range:

SELECT *
FROM presences
WHERE DATE(start) <= '2022-04-06' AND DATE(end) >= '2022-04-04';

See the demo.

CodePudding user response：

You dont need to add DATE if the column is date type already, perhaps, if its not date, you need to cast it to date. But this would work:

SELECT *
FROM presences
WHERE start >= '2022-04-04' AND end <= '2022-04-06';