Ranges of dates overlapping

Question

I have two variables containing dates. DateStart and DateEnd (in SQL).
I also have two DatePicker controls (in a WinForm).
I import the two dates from the database and need to do something tricky.

So the two variables create a range of dates and the two date pickers create another range of date.
How can I check if these dates range are overlapping with a Sql query ?

E.g (yyyy/mm/dd format)

DateStart = 2012/07/01, DateEnd = 2012/07/31

| DatePicker1  | DatePicker2 | Overlapping | 
--------------------------------------------
| 2012/07/15   | 2012/07/16  |    True     |
--------------------------------------------
| 2012/07/31   | 2012/08/01  |    True     |
--------------------------------------------
| 2012/06/20   | 2012/07/01  |    True     |
--------------------------------------------
| 2012/08/01   | 2012/09/01  |    False    |
--------------------------------------------

I know it's a little messed up but I didn't know how else to ask this.

score 3 · Accepted Answer · edited May 23 '17 at 12:05

3

Two date ranges overlap if start of first range is before end of second range and end of first range is after start of second range. So:

where DateStart <= @DatePicker2
  and DateEnd >= @DatePicker1

A good explanation is this way.

edited May 23 '17 at 12:05

Community

1
1

answered Aug 03 '12 at 13:53

Nikola Markovinović

18,963
5
46
51

score 0 · Answer 2 · answered Aug 03 '12 at 13:59

Use this function

/* 
 * Tests if two given periods overlap each other.
 *
 * @TS Test period start
 * @TE Test period end
 * @BS Base period start
 * @BE Base period end
 */
CREATE FUNCTION [fn_DateTime_Overlap]
(
    @TS DATETIME = NULL,
    @TE DATETIME = NULL,
    @BS DATETIME = NULL,
    @BE DATETIME = NULL
)
RETURNS BIT
AS
BEGIN
    -- More simple?
    -- return !((TS < BS && TE < BS) || (TS > BE && TE > BE));
    -- The version below, without comments 
    -- (TS >= BS && TS < BE) || (TE <= BE && TE > BS) || (TS <= BS && TE >= BE)
    IF (
        -- 1. Case:
        --       TS-------TE
        --    BS------BE
        -- TS is after BS but before BE
        (@TS >= @BS AND @TS < @BE)
        -- 2. Case
        --    TS-------TE
        --        BS---------BE
        -- TE is before BE but after BS
        OR (@TE <= @BE AND @TE > @BS)
        -- 3. Case
        --  TS----------TE
        --     BS----BE
        -- TS is before BS and TE is after BE
        OR (@TS <= @BS AND @TE >= @BE)
    ) RETURN 1
    RETURN 0
END

Impressive. I prefere the compact version though :-P – phadaphunk Aug 03 '12 at 14:01 — phadaphunk, Aug 03 '12 at 14:01

Ranges of dates overlapping

2 Answers2