查找日期范围与指定日期范围相交的行

Question

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.

Answer 1

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'