select distinct a.keycolumn

from tablex a

join tablex b ON a.startdate between b.startdate and b.enddate

OR a.enddate between b.startdate and b.enddate

would only return cases where the range a.startdate to a.enddate starts or ends between b.startdate and b.enddate.

if the a range starts before and ends after, what happens?

or (a.enddate > b.enddate and a.startdate < b.enddate)

should take care of it.