TSQL - checking for the intersection of two sets of dates

Double post day!

The same query that had concatenation happening from the previous post also had date range parameters, @StartDate and @EndDate, and needed to only return records (which also had their own start/end date fields) in which the records’ were active during the specified date range.  I.e. where the record’s date range intersected with the date range parameters.  It confused me for a bit so I’m recording for posterity. As I see it, there are three possible valid cases:

Case 1: Record’s start date is within the date range

Case 3: Record’s end IS NULL (i.e. still currently active) and record’s start is less than the end of the date range

So expressed in code, these three cases looked like this:

SELECT SomeFields<o:p></o:p>

FROM  SomeTable t<o:p></o:p>

–case 1<o:p></o:p>

WHERE ((t.Start >=@StartDate<o:p></o:p>

AND   t.Start < @EndDate)<o:p></o:p>

–case 2<o:p></o:p>

OR    (t.End > @StartDate<o:p></o:p>

AND   t.End <= @EndDate)<o:p></o:p>

–case 3<o:p></o:p>

OR    (t.End IS NULL<o:p></o:p>

AND   t.Start < @EndDate))<o:p></o:p>

If you notice any issues or potential for improvement, please let me know! :)