OVERLAPS predicate

The OVERLAPS predicate determines whether two chronological periods overlap. A chronological period is specified by a pair of date-time expressions (the first expression specifies the start of a period; the second specifies its end).

Each of the date-time expressions (start1, end1, start2, and end2) must return a value that is a DATE, TIME, or TIMESTAMP:

If start1 returns a DATE or TIMESTAMP value, the other expressions must all return either a DATE or TIMESTAMP value; otherwise, error SQL0401N is returned. The default time associated with a returned DATE value is 00:00.
If start1 returns a TIME value, the other expressions must all return a TIME value; otherwise, error SQL0401N is returned.

The begin and end values are not included in the periods. For example, the periods 2016-10-19 to 2016-10-20 and 2016-10-20 to 2016-10-21 do not overlap.

If none of the expressions return a null value, the OVERLAPS predicate returns true if the time periods overlap and false if they do not. If one or more of the date-time expressions returns a null value, that influences the result:

If one of the expressions returns a null value, the result is true if the other value for that period falls within the other period; otherwise, the result is NULL.
If two or more of the expressions return a null value, the result is NULL.

Examples

The following statement returns rows because the OVERLAPS predicate is true (the period 17-21 March 2016 overlaps the period 20-22 March 2016):

 SELECT * from T1 where (cast('2016-03-17' as DATE), 
       cast('2016-03-21' as DATE)) OVERLAPS
       (cast('2016-03-20' as DATE), cast('2016-03-22' as DATE));

The following statement does not return rows because the OVERLAPS predicate is false (the begin and end values are not included in the periods, so the period 19-20 October 2016 does not overlap the period 20-21 October 2016):

 SELECT * from T1 where (cast('2016-10-19' as DATE), 
       cast('2016-10-20' as DATE)) OVERLAPS
       (cast('2016-10-20' as DATE), cast('2016-10-21' as DATE));

The following statement returns rows because the OVERLAPS predicate is true (the date 22 March 2016 is within the period 20-23 March 2016):

 SELECT * from T1 where (cast('2016-10-22' as DATE),NULL) OVERLAPS
       (cast('2016-10-20' as DATE), cast('2016-10-23' as DATE));