CODEWITHSUNDEEP
sqlvb.netdatedate-range
user191766
user191766

Reputation:

check on overlapping dates (vb.net)

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?

Upvotes: 3

Views: 2779

Answers (3)

Andomar
Andomar

Reputation: 238068

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

Upvotes: 2

pierrotlefou
pierrotlefou

Reputation: 40721

Below is equivalent to what Garry suggest

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

Upvotes: 9

Garry Shutler
Garry Shutler

Reputation: 32698

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.

Upvotes: 4

Related Questions