DECLARE @inputDate DATETIME
SET @inputDate = '01/16/2011'
SELECT * FROM YourTable WHERE @inputDate BETWEEN BeginDate AND EndDate
union
SELECT * FROM (
SELECT TOP 1 * FROM YourTable WHERE NOT EXISTS(SELECT NULL FROM YourTable WHERE @inputDate BETWEEN BeginDate AND EndDate)
AND EndDate < @inputDate
ORDER BY EndDate DESC
) A
UNION
SELECT TOP 1 * FROM YourTable WHERE NOT EXISTS(SELECT NULL FROM YourTable WHERE @inputDate BETWEEN BeginDate AND EndDate)
AND BeginDate > @inputDate
ORDER BY BeginDate desc
SELECT * FROM Table_Name WHERE BeginDate <= @date_field and EndDate >= @date_field
-- where @date_field is parameter passed by user...