How to check in MySQL if any date in a range of dates falls between dates held in a table

Question

I currently have a table containing a series of dates that staff are taking holidays:

DROP TABLE IF EXISTS `holidays`;

CREATE TABLE `holidays` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `staffid` int(11) DEFAULT NULL,
  `startdate` datetime DEFAULT NULL,
  `enddate` datetime DEFAULT NULL,
  PRIMARY KEY (`id`)
);

INSERT INTO `holidays` (`id`, `staffid`, `startdate`, `enddate`)
VALUES
    (1,1,'2016-01-25 12:45:00','2016-01-29 12:30:00'),
    (2,1,'2016-01-18 09:00:00','2016-01-18 12:45:00'),
    (3,1,'2016-02-29 09:00:00','2016-03-04 16:30:00'),
    (4,1,'2016-01-19 12:30:00','2016-01-19 15:00:00');

What I want to do is check whether any date in this working week (mon-fri) falls between any of the start and enddates in the table. This seems to be to require looping through each of the dates in this working week, but I do not know if this is possible in MySQL. I'd rather not loop through using PHP to send off multiple queries as that would probably end up being slow.

Any suggestions?

Possible duplicate of [Determine Whether Two Date Ranges Overlap](http://stackoverflow.com/questions/325933/determine-whether-two-date-ranges-overlap) — Martin Schneider, Feb 01 '16 at 12:34

score 2 · Answer 1 · edited May 23 '17 at 12:08

Martin Schneider's comment above helped me arrive at the answer. It wasn't MySQL specific but rather the logical approach to solving the problem, so I am including the query I am using below to help anyone who comes across this in the future:

SELECT * FROM holiday 
WHERE staffid = {staff id} 
AND '{date on monday}' <= DATE(enddate) 
AND '{date on friday}' >= DATE(startdate)

Replace the text inside the curly brackets as required.

How to check in MySQL if any date in a range of dates falls between dates held in a table

1 Answers1

Linked