JRG JRG - 5 months ago 10x
SQL Question

Query to find records that were active within a range of dates

I have a database table containing the start and expire date of employee certifications. I need to query the table to get employees who were certified during a date range.

Example table:

EmpID | FromDate | ExpireDate
1 | 2/1/2011 | 3/1/2015
2 | 10/1/2010 | 2/1/2013
3 | 3/1/2013 | 5/30/2013
4 | 11/1/2000 | 3/1/2012
5 | 5/6/2013 | 5/30/2017

If a user wants to find employees that were certified between 5/1/2013 and 5/30/2013 they should get back employee ids 1, 3, and 5. Note that employee id 5 was certified during the date range though the FromDate is after the start date of the query (5/1/2013).

Hope that makes sense. I'm having a hard time wrapping my head around how to write the query.


Assuming that your dates are really stored as dates, the following should work:

select t.*
from t
where FromDate <= '5/30/2013' and
      EndDate >= '5/1/2013';

The logic is that FromDate is less than the end of the period and EndDate is after the beginning. That will get any overlap at all with the period.