Intersecting date intervals - the easy way

I guess this is a common situation were you have a task to filter a list of objects that fall into some date range. "Get all my tasks entered today", "Get all my payments made through the last week " - those could be quite realistic examples.

SELECT * FROM dbo.Tasks 
    WHERE tsk_DateCreated BETWEEN @today_start AND @today_end

  SELECT * FROM dbo.Payments
    WHERE pmt_DateMade BETWEEN @week_start_date AND @week_end_date

Those are quite easy scenarios, as you only have a single date to put into the interval: tsk_DateCreated for tasks and pmt_DateMade for payments. Things get little more complicated when you have two date fields come into play.

Examples: "Get all employee vacation list that intersect with a given period", "Get all user accounts that were valid during the given interval".

Solution (the hard way):

SELECT * FROM dbo.EmployeeVacations
    WHERE (empv_DateFrom BETWEEN @date_from AND @date_to) OR 
          (empv_DateTo BETWEEN @date_from AND @date_to) OR 
          (empv_DateFrom <= @date_from AND empv_DateTo >= @date_to) 

  SELECT * FROM dbo.Users
    WHERE (usr_DateFrom BETWEEN @date_from AND @date_to) OR 
          (usr_DateTo BETWEEN @date_from AND @date_to) OR 
          (usr_DateFrom <= @date_from AND usr_DateTo >= @date_to) 

Solution (the easy way):

SELECT * FROM dbo.EmployeeVacations
    WHERE (NOT((empv_DateTo < @date_from) OR (empv_DateFrom > @date_to)))

  SELECT * FROM dbo.Users
    WHERE (NOT((usr_DateTo < @date_from) OR (usr_DateFrom > @date_to)))

Give it a try, you will not regret it.

Update:  another way to check for date interval intersection:

SELECT * FROM dbo.EmployeeVacations
    WHERE (@date_from BETWEEN empv_DateFrom AND empv_DateTo OR empv_DateFrom BETWEEN @date_from AND @date_to)

  SELECT * FROM dbo.Users
    WHERE (@date_from BETWEEN usr_DateFrom AND usr_DateTo OR usr_DateFrom BETWEEN @date_from AND @date_to)