Pull Date-Effective Records that Intersect A Given Range (See related posts)

-- @rangeStart is the start of the date range of interest
-- @rangeEnd is the end of the date range of interest
-- If you use null to signify infinity, then use the commented line instead of the one above it.
select *
from DateEffectiveTable
where StartDate < @rangeEnd
  and EndDate >= @rangeStart
  -- and coalesce(endDate, '9999-12-31') >= @rangeStart