Checking if two ranges of dates overlap

[TomHW]

I would like to know if there is any way to determine if a range of dates overlaps another range of dates. For instance:

Events occur over a matter of days.
I would like to be able to search for all events that were occuring within a certain time period.

Event 1: 03/38/04 - 03/30/04
Event 2: 01/02/04 - 02/15/04
Event 3: 02/05/04 - 03/19/04

Search for events occuring between 02/01/04 and 04/01/04 would return all events.
Search for events occuring between 01/20/04 and 03/20/04 would return Events 2 & 3.

I thought about checking to see if the first date was between each events dates and then checking the end date between event dates but that will exclude events which begin and end inside of the date range. Is there any way to do this without checking to see if each individual day in a date range is within the other date ranges?

Thanks,
Tom

 

[SkipVought]

Hi,

You have a Start and End date for testing.

If any Event_End < Start OR Event_Start > End, then you do not have an intersection, otherwise you do.

Skip,

_{[red]Be advised:[/red] When transmitting sheet music...
If it ain't baroque, don't fax it!}

 

[PHV]

Something like this ?
SELECT * FROM tblEvents
WHERE eventStart<=endDate AND eventEnd>=firstDate

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[Zion7]

Another thought may be, the DateDiff()

Compare days within event1, let's say 8

then compare start of event1 with start of event2
If less then 8, then the remainder, let's say 3, are those which overlap( so last 3, of event 1), otherwise, they don't.
do same with start event1, with start event3.

Hope this helps.

Good Luck!

 

[TomHW]

Thanks Skip and PHV,

It's always the simple answers that seem to elude you. I've included the code from PHV since my code runs from true on the If Statement and doesn't require an Else.

Tom