CODEWITHSUNDEEP
mysqldatabase
I'll-Be-Back
I'll-Be-Back

Reputation: 10828

Date Overlapping Result

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'

Upvotes: 0

Views: 46

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269543

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.

Upvotes: 1

Related Questions