Please see attached image
I have a table which have FromDate and ToDate. FromDate is start of some event and ToDate is end of taht event. I need to find a record if search criteria is in between range of dates.
e.g.
If a record has FromDate 2010/15/5 and ToDate 2010/15/25 and my criteria is FromDate 2010/5/18 and ToDate is 2010/5/21 then this record should be in search results becasue this is in the range of 15 to 25.
Following is my search query (chunk of)
SELECT m.EventId
FROM MajorEvents
WHERE ( (m.LocationID = @locationID OR @locationID IS NULL) OR M.LocationID IS NULL)
AND (
CONVERT(VARCHAR(10),M.EventDateFrom,23) BETWEEN CONVERT(VARCHAR(10),@DateTimeFrom,23) AND CONVERT(VARCHAR(10),@DateTimeTo,23)
OR
CONVERT(VARCHAR(10),M.EventDateTo,23) BETWEEN CONVERT(VARCHAR(10),@DateTimeFrom,23) AND CONVERT(VARCHAR(10),@DateTimeTo,23)
)
If Search Criteria is equal to FromDate or ToDate then results are ok e.g. If search criterai is DateFrom = 2010/5/15 AND DateTo = 2010/5/18 then this record will return becasue Date From is exactly what is DateFrom in db.
OR
If search criterai is DateFrom = 2010/5/22 AND DateTo = 2010/5/25 then this record will return becasue Date To is exactly what is DateTo in db
But if anything in between this range it does not work
Thanks for the help.
