check conflict between two datetimepicker

Question

So I'm making a simple reservation system ,C# and sql . I have datetimepicker 1 and datetimepicker 2 . For ex. datetimepicker = aug 1 2009 and datetimepicker2 = aug 3 2009 saved in sql by the user .

What to do with that so that the next customer that would try to get the date 1-3 would be forced to choose another date ?

This already has an answer; [the reference is MySQL](http://stackoverflow.com/questions/143552/comparing-date-ranges), but the technique is DB-agnostic (as the existing answer demonstrates). Note that, given you should be [using an exclusive upper-bound](http://stackoverflow.com/questions/143552/comparing-date-ranges/143568#143568), it should be `NOT (range_start < @check_period_end ...` — Clockwork-Muse, Nov 30 '14 at 11:45

peter.petrov · Answer 1 · 2014-11-30T11:29:00.010

You can run a query like this one:

select count(*) as cnt
from reservations t
where
not 
(

    (@dtTo <= t.DateFrom)

    or

    (@dtFrom >= t.DateTo)

)

assuming that @dtFrom, @dtTo are the dates the second user has chosen, and assuming
you have a reservations table with DateFrom and DateTo columns.

If this query returns 0, then allow the second user to save.
If it returns a number > 0, then don't allow the second user to save.

What's the idea behind this:
two segments of time [dateFrom1, dateTo1] and [dateFrom2, dateTo2] are not
conflicting (i.e. are not overlapping) for this reservation-based scenario if and only if:
dateTo1 <= dateFrom2 or dateFrom1 >= dateTo2
(draw it on a piece of paper and you'll see why).
So if the negation
not ( dateTo1 <= dateFrom2 or dateFrom1 >= dateTo2 )
is true, the two segments are overlapping/conflicting.

check conflict between two datetimepicker

1 Answers1