HiveBrain v1.2.0
Get Started
← Back to all entries
patternsqlModerate

Check if two datetimes are between two others

Submitted by: @import:stackexchange-dba··
0
Viewed 0 times
areotherstwobetweendatetimescheck

Problem

Today I'm facing with problem, which I can't solve.

I need to check if two datetimes (2019-04-08 13:30:00 - 2019-04-08 15:30:00 ) are between or overlap two others (2019-04-08 14:00:00 - 2019-04-08 16:00).

Imagine that those datetimes are event's date. We can see that there is an event between 14:00 and 16:00 so we cant' have other between.

I've tried something like this:

SELECT * FROM Rezerwacja WHERE
(('2019-04-08 13:30:00.000' between Start and Stop) OR
('2019-04-08 15:30:00.000' between Start and Stop))


How to check if there is an "event" already ?

Solution

To check for overlaps, you only need 2 conditions, the start or interval A with the end of interval B, and vice versa:

WHERE
      '2019-04-08 13:30:00.000' < Stop 
  AND Start < '2019-04-08 15:30:00.000'

Code Snippets

WHERE
      '2019-04-08 13:30:00.000' < Stop 
  AND Start < '2019-04-08 15:30:00.000'

Context

StackExchange Database Administrators Q#234187, answer score: 10

Revisions (0)

No revisions yet.