user1381745 user1381745 - 6 months ago 10
SQL Question

SQL mental block - checking if one period of time overlaps another

I have four unix times. Two are the beginning and end of "today" (whichever that day may be) - i.e. 00:00:00 and 23:59:59 - and the other two are user-chosen times.

What I need is a (TSQL-friendly) query to check if the period between "today" times overlaps with the user's times. Can anyone help?

Answer

I have this in a txt doc to remember (date dyslexia):

Intersects:

Date Range 1   |         |>----------------------<|
Date Range 2   |                 |>------------------------<|

where Range1Start <= Range2End and Range1End >= Range2Start

Range 1 in Range 2:

Date Range 1   |                   |>-------------<|
Date Range 2   |                 |>------------------------<|


where Range1Start >= Range2Start and Range1End <= Range2End

Range 2 in Range 1:

Date Range 1   |         |>----------------------<|
Date Range 2   |                 |>-------------<|


where Range2Start >= Range1Start and Range2End <= Range1End