Selecting records within a given date range is one of the common requirement these days, but many people find it difficult. The most common mistake people make is always try to do the comparison as the same as the way we do for numbers as shown below
Wrong comparison
SELECT *
FROM Table
WHERE StartDate >= @P_StartDate
AND EndDate =< @P_EndDate
This wills not retrieve the qualified records as the comparison will not be against the range instead it will be against two dates and it will ignore any records fall within the range. The trick is to change the parameter to check the date in the reverse order as shown below.
Modified Query
SELECT *
FROM Table
WHERE EndDate > = @P_StartDate
AND StartDate =< @P_EndDate
