I want to get all the movie ids and names for a given date range. e.g. 2012-02-01 to 2012-02-20 should show ids( 1, 2 and 3) its like now showing movies on a given date range.
movie{ id, name, start_date, end_date, visibility} id name start_date end_date visibility 1 abc 2012-01-05 2012-02-10 1 2 xyz 2012-02-05 2012-02-25 1 3 cde 2012-02-19 2012-02-27 1 4 ghi 2012-03-01 2012-03-20 1 Guessing that you want to find the overlap, assuming the parameter values are in a table named params:
SELECT * FROM movie AS m, params AS p WHERE CASE WHEN m.start_date > p.start_date THEN m.start_date ELSE p.start_date END < CASE WHEN m.end_date > p.end_date THEN p.end_date ELSE m.end_date END; 
WHERE m.start_date <= p.end_date AND p.start_date <= m.end_date equivalent?
We have two intervals:
the FieldInterval := (start_date, end_date)
and the TestInterval := (@StartDate, @EndDate), defined as
@StartDate = '2012-02-01' @EndDate = '2012-02-20' To test if FieldIntervalis inside TestInterval:
SELECT id FROM movie WHERE @StartDate <= start_date AND end_date <= @EndDate To test if TestInterval is inside FieldInterval:
SELECT id FROM movie WHERE start_date <= @StartDate AND @EndDate <= end_date To test if the two intervals overlap:
SELECT id FROM movie WHERE start_date <= @EndDate AND @StartDate <= end_date select id from movie where start_date between '2012-02-01' and '2012-02-20' and end_date between '2012-02-01' and '2012-02-20' and start_date <= end_date; I assume you want movies which start_date and end_date fit into 2 parameters, right?
select id, name from movie where @startDate < create_date and end_date< @endDate If you inform 2012-02-01 and 2012-02-20 it will return all the movies which have start_date and end_date between that range
select id, name from `movie` where end_date >= '2012-01-05' and start_date <= '2012-01-20' This works for me and its looks simple.