Overlapping dates

[Boogiebaby2]

Hi, trying to work out how to check wether Form.startDate and Form.endDate fall in between other start or end dates in the database... currently using

SELECT cmsPollsID
FROM cmsPolls
WHERE '#Trim(FORM.eventStartDate)#' BETWEEN cmsPolls.liveStartDate and cmsPolls.liveEndDate
AND '#Trim(FORM.eventEndDate)#' BETWEEN cmsPolls.liveStartDate and cmsPolls.liveEndDate

works when the start date is between dates but not the end date.

Thanks

[Dan_Bracuk]

First, if you are using the trim function, you may be storing your dates as char instead of date datatype. Bad idea

Next, what are some of the values where you don't think it's working?

[cf_dev2]

I agree about the dates. Storing date values as strings is bad news.

If you want to check whether either the start or end date fall between another range, use OR instead of AND. You may also want to check whether they span another range.

[Boogiebaby2]

Thanks heaps for that
that worked heaps better !