This article presents simple methods to test for overlapping date ranges.
Related articles.
The examples in the this article require the following table.
CREATE TABLE dates_test ( id NUMBER, start_date DATE, end_date DATE ); INSERT INTO dates_test VALUES (1, TO_DATE('01-JAN-2012','DD-MON-YYYY'), TO_DATE('05-JAN-2012','DD-MON-YYYY')); INSERT INTO dates_test VALUES (2, TO_DATE('05-MAR-2012','DD-MON-YYYY'), TO_DATE('08-MAR-2012','DD-MON-YYYY')); INSERT INTO dates_test VALUES (3, TO_DATE('04-MAR-2012','DD-MON-YYYY'), TO_DATE('07-MAR-2012','DD-MON-YYYY')); INSERT INTO dates_test VALUES (4, TO_DATE('06-MAR-2012','DD-MON-YYYY'), TO_DATE('09-MAR-2012','DD-MON-YYYY')); INSERT INTO dates_test VALUES (5, TO_DATE('06-MAR-2012','DD-MON-YYYY'), TO_DATE('07-MAR-2012','DD-MON-YYYY')); INSERT INTO dates_test VALUES (6, TO_DATE('04-MAR-2012','DD-MON-YYYY'), TO_DATE('09-MAR-2012','DD-MON-YYYY')); COMMIT;
The following procedure accepts start and end date parameters. If the specified date range overlaps with one already present in the table it raises an exception.
CREATE OR REPLACE PROCEDURE date_clash ( p_start_date IN DATE, p_end_date IN DATE ) AS l_count NUMBER(1) := 0; BEGIN SELECT COUNT(*) INTO l_count FROM dates_test WHERE start_date <= p_end_date AND end_date >= p_start_date AND ROWNUM = 1; IF l_count > 0 THEN RAISE_APPLICATION_ERROR(-20000, 'Dates range clashes with existing dates.'); END IF; END date_clash; /
The following output shows a number of tests, reflecting all the possible overlapping situations.
-- Date range completely before that in table. EXEC date_clash(TO_DATE('01-DEC-2011','DD-MON-YYYY'), TO_DATE('31-DEC-2011','DD-MON-YYYY')); PL/SQL procedure successfully completed. SQL> -- Date range spans the start of one in the table. EXEC date_clash(TO_DATE('01-DEC-2011','DD-MON-YYYY'), TO_DATE('02-JAN-2012','DD-MON-YYYY')); BEGIN date_clash(TO_DATE('01-DEC-2011','DD-MON-YYYY'), TO_DATE('02-JAN-2012','DD-MON-YYYY')); END; * ERROR at line 1: ORA-20000: Dates range clashes with existing dates. ORA-06512: at "TEST.DATE_CLASH", line 16 ORA-06512: at line 1 SQL> -- Date range completely within a range in the table. EXEC date_clash(TO_DATE('02-JAN-2012','DD-MON-YYYY'), TO_DATE('02-JAN-2012','DD-MON-YYYY')); BEGIN date_clash(TO_DATE('02-JAN-2012','DD-MON-YYYY'), TO_DATE('02-JAN-2012','DD-MON-YYYY')); END; * ERROR at line 1: ORA-20000: Dates range clashes with existing dates. ORA-06512: at "TEST.DATE_CLASH", line 16 ORA-06512: at line 1 SQL> -- Date range completely spans a range within the table. EXEC date_clash(TO_DATE('31-DEC-2011','DD-MON-YYYY'), TO_DATE('07-JAN-2012','DD-MON-YYYY')); BEGIN date_clash(TO_DATE('31-DEC-2011','DD-MON-YYYY'), TO_DATE('07-JAN-2012','DD-MON-YYYY')); END; * ERROR at line 1: ORA-20000: Dates range clashes with existing dates. ORA-06512: at "TEST.DATE_CLASH", line 16 ORA-06512: at line 1 SQL> -- Date range spans the end of one in the table. EXEC date_clash(TO_DATE('02-JAN-2012','DD-MON-YYYY'), TO_DATE('31-JAN-2012','DD-MON-YYYY')); BEGIN date_clash(TO_DATE('02-JAN-2012','DD-MON-YYYY'), TO_DATE('31-JAN-2012','DD-MON-YYYY')); END; * ERROR at line 1: ORA-20000: Dates range clashes with existing dates. ORA-06512: at "TEST.DATE_CLASH", line 16 ORA-06512: at line 1 SQL> -- Date range after any in the table. EXEC date_clash(TO_DATE('06-JAN-2012','DD-MON-YYYY'), TO_DATE('07-JAN-2012','DD-MON-YYYY')); PL/SQL procedure successfully completed. SQL>
You can use a similar approach when looking for overlapping dates in existing data.
SELECT a.* FROM (SELECT * FROM dates_test ORDER BY id) a WHERE EXISTS (SELECT 1 FROM dates_test b WHERE b.start_date <= a.end_date AND b.end_date >= a.start_date AND b.id < a.id); ID START_DATE END_DATE ---------- -------------------- -------------------- 3 04-MAR-2012 00:00:00 07-MAR-2012 00:00:00 4 06-MAR-2012 00:00:00 09-MAR-2012 00:00:00 5 06-MAR-2012 00:00:00 07-MAR-2012 00:00:00 6 04-MAR-2012 00:00:00 09-MAR-2012 00:00:00 4 rows selected. SQL>
For more information see:
Hope this helps. Regards Tim...
Back to normal view: https://oracle-base.com/articles/misc/overlapping-date-ranges