CODEWITHSUNDEEP
mysqlsqldatabase
willkara
willkara

Reputation: 177

How to determine if a date range overlaps any part of a specific month?

The questions is this.

I want to have the list of all the performances that ran in November 1999. It doesn't matter if the engagements started before November or ended after November.

Table for Engagements is EA_Engagements and the field names are

They are both in DATE form.

Upvotes: 1

Views: 310

Answers (3)

Niraj Kumar
Niraj Kumar

Reputation: 754

Answer by 'Nedret Recep' worked in my case with similar but different scenario. I wanted to find whether a given daterange is overlapping any other events daterange in the database.

Table (Master Blocking Dates)
mbd_id  mbd_from_date   mbd_to_date mbd_reason
1   2013-10-16  2013-10-19  Demo Blocking 1
2   2013-10-21  2013-10-24  Demo2
3   2013-10-31  2013-11-04  Demo3

and my query based on 'Nedret Recep' suggestion was this - 

SELECT * FROM ib_master_blocked_dates WHERE venue_id=$venue_id AND 
(
    (mbd_from_date BETWEEN '$from_date' AND '$to_date') 
        OR
    (mbd_to_date BETWEEN  '$from_date' AND '$to_date')
        OR
    ('$to_date' BETWEEN mbd_from_date AND mbd_to_date)
        OR
    ('$from_date' BETWEEN mbd_from_date AND mbd_to_date)
)

Result:

mbd_id  mbd_from_date   mbd_to_date mbd_reason
1   2013-10-16  2013-10-19  Demo Blocking 1
2   2013-10-21  2013-10-24  Demo2

Upvotes: 0

Saic Siquot
Saic Siquot

Reputation: 6513

select * 
from EA_Engagements 
where StartDate <= '1999-11-30' 
  and EndDate   >= '1999-11-01'

Upvotes: 5

Nedret Recep
Nedret Recep

Reputation: 728

here it is:

SELECT
    name, StartDate, EndDate
FROM
    EA_Engagements
WHERE
    StartDate BETWEEN '1999-11-01' AND '1999-11-30' OR
    Enddate BETWEEN  '1999-11-01' AND '1999-11-30'

Upvotes: -1

Related Questions