CODEWITHSUNDEEP
phpmysqlsqldate
flawless
flawless

Reputation: 51

Find rows where date range intersect the specified date range

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.

Upvotes: 1

Views: 330

Answers (2)

Salman Arshad
Salman Arshad

Reputation: 272006

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'

Upvotes: 0

Joni
Joni

Reputation: 111219

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.

Upvotes: 1

Related Questions