Date Overlapping Result

Question

In the hire table, there are date range between start_date and end_date

Client choose between two dates to hire, it need to make sure it does not overlap between start_date and end_date that already exist in the table

For example in the hire table

id,    start_date,           end_date
-------------------------------------------------
3   2015-10-23 00:00:00,     2015-10-30 23:59:59

SQL Query:

SELECT *  FROM `hire` WHERE DATE(`start_date`) >= '2015-10-24' AND DATE(`end_date`) <= '2015-10-26'

For some reason, it is not showing any result?

I also need to show the result because end_date is over-lapped as well

DATE(`start_date`) >= '2015-10-27' AND DATE(`end_date`) <= '2015-10-31'

This should show no result:

DATE(`start_date`) >= '2015-10-31' AND DATE(`end_date`) <= '2015-11-02'

Possibly related to http://stackoverflow.com/questions/19924236/query-comparing-dates-in-sql — Manu, Nov 04 '15 at 12:35
In your example start_date is 2015-10-23. The dates you are comparing with are always later. Therefore start_date is always less than and never greater or equal. — CFreitas, Nov 04 '15 at 12:39

score 1 · Answer 1 · answered Nov 04 '15 at 12:36

1

I suspect that the logic you want is this:

SELECT h.*
FROM `hire` h
WHERE DATE(`start_date`) <= '2015-10-26' AND
      DATE(`end_date`) >= '2015-10-24';

This gets anyone who is active during that period of time.

answered Nov 04 '15 at 12:36

Gordon Linoff

That kinda work but if I choose later end date, it should still show result because it is overlapped: For example: `DATE(`start_date`) <= '2015-10-24' AND DATE(`end_date`) >= '2015-11-05'` – I'll-Be-Back Nov 04 '15 at 12:41

1 Answers1