DBA Hub

📋Steps in this guide1/3

Overlapping Date Ranges

This article presents simple methods to test for overlapping date ranges.

oracle miscconfigurationintermediate
by OracleDba
11 views
1

Setup

The examples in the this article require the following table.

Code/Command (click line numbers to comment):

1
2
3
4
5
6
7
8
9
10
11
12
13
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;
2

One-Off Date Range Test

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. The following output shows a number of tests, reflecting all the possible overlapping situations.

Code/Command (click line numbers to comment):

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
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;
/

-- 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>
3

Check Existing Data

You can use a similar approach when looking for overlapping dates in existing data. For more information see: - Non-Overlapping Dates Constraint Hope this helps. Regards Tim...

Code/Command (click line numbers to comment):

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
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>

Comments (0)

Please to add comments

No comments yet. Be the first to comment!