CREATE TABLE #Sample (moviename char(1), movievalid_from datetime, movievalid_to datetime)
INSERT INTO #Sample VALUES('A', '2014-09-11', '2014-09-13')
INSERT INTO #Sample VALUES('B', '2014-09-12', '2014-09-12')
SELECT MovieName
FROM #Sample
WHERE '2014-9-11' BETWEEN movievalid_from AND movievalid_to
/*
MovieName
A
*/
SELECT MovieName FROM #Sample
WHERE '2014-9-12' BETWEEN movievalid_from AND movievalid_to
/*
MovieName
A
B
*/
SELECT MovieName
FROM #Sample
WHERE '2014-9-13' BETWEEN movievalid_from AND movievalid_to
/*
MovieName
A
*/
SELECT MovieName
FROM #Sample
WHERE '2014-9-14' BETWEEN movievalid_from AND movievalid_to
-- No Records found for the above search criteria
DROP TABLE #Sample