CODEWITHSUNDEEP
phpmysqlsql
Raywin
Raywin

Reputation: 53

select date range which overlap a certain date

Lets say this my table like below.

ID | Startdate           | Enddate
-----------------------------------------------
1  | 2012-12-12 08:00:00 | 2012-12-15 18:30:00
-----------------------------------------------
2  | 2012-12-11 00:00:00 | 2012-12-16 00:00:00
-----------------------------------------------
3  | 2012-12-16 08:00:00 | 2012-12-17 18:30:00
-----------------------------------------------
4  | 2012-12-13 00:00:00 | 2012-12-14 11:30:00
-----------------------------------------------

the selected date range is Id 1, 2012-12-12 08:00:00 to 2012-12-15 18:30:00 . I wanna select any date which will across the date range.

The correct Id will be, 2 and 4 . Hope you guys understand, if any question please feel free to ask. Thank you.

Upvotes: 4

Views: 144

Answers (2)

fthiella
fthiella

Reputation: 49049

select d1.*
from
  dates d1 inner join dates d2
  on d2.id=1 and d1.Startdate<d2.Enddate and d1.Enddate>d2.Startdate
where d1.id<>1

Upvotes: 2

Mahmoud Gamal
Mahmoud Gamal

Reputation: 79899

SELECT t1.*
FROM YourTable t1
INNER JOIN
(
   SELECT StartDate, EndDate
   FROM YourTable 
   WHERE Id = 1
) t2 ON t2.StartDate < t1.StartDate AND t2.EndDate > t1.EndDate

Upvotes: 1

Related Questions