The following slice of code will search a table and return any rows where there is a clash between the date range in the row (ValidFrom and ValidTo) and the date range in the variables (@InTo and @InFrom).

Null dates are treated as either infinite past or infinite future accordingly.

WHERE
NOT (
(@InTo < ValidFrom AND @InTo IS NOT NULL AND ValidFrom IS NOT NULL)
OR
(@InFrom > ValidTo AND @InFrom IS NOT NULL AND ValidTo IS NOT NULL)
)