check on overlapping dates (vb.net)

Question

Hi

in a program I'm writing I'm creating some objects with a start date (with a datepicker) and an end date (also with a datepicker). Now I need to check if this object's date range overlaps with any other object's date range stored in the database. If it does I can't save it in the database but if it doesn't I can.

Anyone has an idea how to do this?

Answer 1

You have 3 scenarios for overlapping: contains start, contains end and wraps range. This can be expressed in SQL like this:

select   count(1)
from     YourTable
where    (@start >= Start and @start <= End) /* contains start */
or       (@end >= Start and @end <= End) /* contains end */
or       (@start < Start and @end > End) /* wraps range */

How you actually issue this query to the database depends on how you're doing data access at the moment.

Answer 2

Below is equivalent to what Garry suggest

select   count(1)
from     YourTable
where    (@start < End and @end > Start)

Answer 3

The simplest overlap check is to see if the new event started before the end of another event, and ended after the start of it. If both conditions are true, the new event overlaps with the old one.

select   *
from     YourTable
where    @start < End and @end > Start