Find rows where date range intersect the specified date range

1
votes

I have a table leave_applications with two columns from date and to date. i need to get those rows from the table which have leave dates falling in a given month, means the rows in which at least one of the dates between from date and to date falls in the given month.

Hence I need a query that will get me rows that have intersection of the two sets of dates (i.e. from_date and to_date) and all dates of that month.

2
phpmysqlsqldate
So what have you tried so far? - v0d1ch
i tried to build a query like Select * from leave_application where (select date() wher month()= 12 and year()=2012) between from_date and to_date; but this doesn't seem to work - flawless

2 Answers

0
votes

I wrote about these queries some time ago. Here is the solution:

-- 2.2) select date ranges that overlap [d1, d2] (d2 and end_date are inclusive)
SELECT * FROM <table> WHERE @d2 >= start_date AND end_date >= @d1

So if you want to check the leaves for Dec 2012, you should write:

SELECT * FROM leave_applications
WHERE '2012-12-31' >= from_date AND to_date >= '2012-12-01'
1
votes

Two ranges A-B and C-D overlap if A < D and B > C. In pseudo-SQL,

select * from leave_applications where 
from_date < (last day of month) AND to_date > (first day of month)

Use <= and >= if you consider it an overlap if from_date or to_date coincide with the first and last days of the month.