Da, am inversat un pic coloanele, e vina mea. Ca sa clarificam, queryul e acum asa:

DBCC DROPCLEANBUFFERS
DBCC FREEPROCCACHE
GO

SELECT *
FROM
    my_table
WHERE 1=1
    AND area_source_id IN (15, 37)
    AND area_sink_id IN (15, 35)   
    AND data_status_id = 1
    AND data_type_id = 2
    AND data_source_id = 3
    AND '2008-09-11 10:00PM' BETWEEN effective_date AND termination_date