OVERLAPS Teradata

Compatibility: Teradata Extension

When working with dates and times, sometimes it is necessary to determine whether two different ranges have common points in time. Teradata provides a Boolean function to make this test for you. It is called OVERLAPS; it evaluates true, if multiple points are in common, otherwise it returns a false. The syntax of the OVERLAPS is:

The following SELECT tests two literal dates and uses the OVERLAPS to determine whether or not to display the character literal:


1 Row Returned

The dates overlap

The literal is returned because both date ranges have from October 15 through November 30 in common.

The next SELECT tests two literal dates and uses the OVERLAPS to determine whether or not to display the character literal:


No Rows Found

The literal was not selected because the ranges do not overlap. So, the common single date of November 30 does not constitute an overlap. When dates are used, 2 days must be involved and when time is used, 2 seconds must be contained in both ranges. The following SELECT tests two literal times and uses the OVERLAPS to determine whether or not to display the character literal:


1 Row Returned

The times overlap

This is a tricky example and it is shown to prove a point. At first glance, it appears as if this answer is incorrect because 02:01:00 looks like it starts 1 second after the first range ends. However, the system works on a 24-hour clock when a date and time (timestamp) is not used together. Therefore, the system considers the earlier time of 2AM time as the start and the later time of 8 AM as the end of the range. Therefore, not only do they overlap, the second range is entirely contained in the first range.

The following SELECT tests two literal dates and uses the OVERLAPS to determine whether or not to display the character literal:

SELECT 'The times overlap' (TITLE ") WHERE (TIME '10:00:00', NULL) OVERLAPS (TIME '01:01:00', TIME '04:15:00') ;
No Rows Found

When using the OVERLAPS function, there are a couple of situations to keep in mind:

  1. A single point in time, i.e. the same date, does not constitute an overlap. There must be at least one second of time in common for TIME or one day when using DATE.
  2. Using a NULL as one of the parameters, the other DATE or TIME constitutes a single point in time versus a range.

All rights reserved © 2018 Wisdom IT Services India Pvt. Ltd DMCA.com Protection Status

Teradata Topics