MySQL 8.0

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
select version();


version()


8.0.30


CREATE TABLE `test_a` (
  `CUSTOMER_RK`         int         DEFAULT NULL,
  `CUSTOMER_STATUS`     varchar(45) DEFAULT NULL,
  `EFFECTIVE_FROM_DTTM` date        DEFAULT NULL,
  `EFFECTIVE_TO_DTTM`   date        DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

INSERT INTO test_a VALUES
  (1, 'A', '2004-01-20', '2015-02-23'),
  (1, 'B', '2016-02-24', '2016-05-17'),
  (1, 'A', '2017-05-18', '9999-12-31'),
  (2, 'B', '1998-09-01', '2018-09-04'),
  (2, 'A', '2018-09-05', '2018-09-06'),
  (2, 'B', '2019-09-07', '2019-09-07'),
  (2, 'A', '2019-09-08', '9999-12-31');

Records: 7  Duplicates: 0  Warnings: 0


SELECT
    CUSTOMER_RK,
    CUSTOMER_STATUS,
    EFFECTIVE_FROM_DTTM,
    EFFECTIVE_TO_DTTM
FROM
    (
        SELECT
            CUSTOMER_RK,
            CUSTOMER_STATUS,
            DATE_ADD( EFFECTIVE_TO_DTTM, INTERVAL 1 DAY ) AS EFFECTIVE_FROM_DTTM,
            DATE_SUB( LEAD( EFFECTIVE_FROM_DTTM, 1, '9999-12-31' ) OVER ( ORDER BY EFFECTIVE_FROM_DTTM ), INTERVAL 1 DAY ) AS EFFECTIVE_TO_DTTM

        FROM
            test_a AS a1
        WHERE
            CUSTOMER_RK = 1
    ) AS a2
WHERE
    EFFECTIVE_FROM_DTTM < EFFECTIVE_TO_DTTM;


CUSTOMER_RK
CUSTOMER_STATUS
EFFECTIVE_FROM_DTTM
EFFECTIVE_TO_DTTM


1
A
2015-02-24
2016-02-23

1
B
2016-05-18
2017-05-17


INSERT INTO test_a (
    CUSTOMER_RK, CUSTOMER_STATUS, EFFECTIVE_FROM_DTTM, EFFECTIVE_TO_DTTM
)
SELECT
    CUSTOMER_RK,
    CUSTOMER_STATUS,
    EFFECTIVE_FROM_DTTM,
    EFFECTIVE_TO_DTTM
FROM
    (
        SELECT
            CUSTOMER_RK,
            CUSTOMER_STATUS,
            DATE_ADD( EFFECTIVE_TO_DTTM, INTERVAL 1 DAY ) AS EFFECTIVE_FROM_DTTM,
            DATE_SUB( LEAD( EFFECTIVE_FROM_DTTM, 1, '9999-12-31' ) OVER ( ORDER BY EFFECTIVE_FROM_DTTM ), INTERVAL 1 DAY ) AS EFFECTIVE_TO_DTTM

        FROM
            test_a AS a1
        WHERE
            CUSTOMER_RK = 1
    ) AS a2
WHERE
    EFFECTIVE_FROM_DTTM < EFFECTIVE_TO_DTTM;

Datetime function: datetime field overflow


INSERT INTO test_a (
    CUSTOMER_RK, CUSTOMER_STATUS, EFFECTIVE_FROM_DTTM, EFFECTIVE_TO_DTTM
)
SELECT
    CUSTOMER_RK,
    CUSTOMER_STATUS,
    EFFECTIVE_FROM_DTTM,
    EFFECTIVE_TO_DTTM
FROM
    (
        SELECT
            CUSTOMER_RK,
            CUSTOMER_STATUS,
            DATE_ADD( NULLIF(EFFECTIVE_TO_DTTM, '9999-12-31'), INTERVAL 1 DAY ) AS EFFECTIVE_FROM_DTTM,
            DATE_SUB( LEAD( EFFECTIVE_FROM_DTTM, 1, '9999-12-31' ) OVER ( ORDER BY EFFECTIVE_FROM_DTTM ), INTERVAL 1 DAY ) AS EFFECTIVE_TO_DTTM

        FROM
            test_a AS a1
        WHERE
            CUSTOMER_RK = 1
    ) AS a2
WHERE
    EFFECTIVE_FROM_DTTM < EFFECTIVE_TO_DTTM;

Records: 2  Duplicates: 0  Warnings: 0


CREATE TABLE t (dt DATE);

/* The SELECT returns NULL as expected */
SELECT '9999-12-31' + INTERVAL 1 DAY;


'9999-12-31' + INTERVAL 1 DAY


null


/* But as soon as we put it into the context of the INSERT it causes the overflow error */
INSERT INTO t VALUES ('9999-12-31' + INTERVAL 1 DAY);

Datetime function: datetime field overflow


/* But we can fix by stopping the overflow from occurring */
INSERT INTO t VALUES (NULLIF('9999-12-31', '9999-12-31') + INTERVAL 1 DAY);
/* If using NULLs to represent an unknown end date the problem does not occur */
TRUNCATE TABLE test_a;
INSERT INTO test_a VALUES
  (1, 'A', '2004-01-20', '2015-02-23'),
  (1, 'B', '2016-02-24', '2016-05-17'),
  (1, 'A', '2017-05-18', NULL),
  (2, 'B', '1998-09-01', '2018-09-04'),
  (2, 'A', '2018-09-05', '2018-09-06'),
  (2, 'B', '2019-09-07', '2019-09-07'),
  (2, 'A', '2019-09-08', NULL);

Records: 7  Duplicates: 0  Warnings: 0


INSERT INTO test_a (
    CUSTOMER_RK, CUSTOMER_STATUS, EFFECTIVE_FROM_DTTM, EFFECTIVE_TO_DTTM
)
SELECT
    CUSTOMER_RK,
    CUSTOMER_STATUS,
    EFFECTIVE_FROM_DTTM,
    EFFECTIVE_TO_DTTM
FROM
    (
        SELECT
            CUSTOMER_RK,
            CUSTOMER_STATUS,
            DATE_ADD( EFFECTIVE_TO_DTTM, INTERVAL 1 DAY ) AS EFFECTIVE_FROM_DTTM,
            DATE_SUB( LEAD( EFFECTIVE_FROM_DTTM, 1, '9999-12-31' ) OVER ( ORDER BY EFFECTIVE_FROM_DTTM ), INTERVAL 1 DAY ) AS EFFECTIVE_TO_DTTM

        FROM
            test_a AS a1
        WHERE
            CUSTOMER_RK = 1
    ) AS a2
WHERE
    EFFECTIVE_FROM_DTTM < EFFECTIVE_TO_DTTM;

Records: 2  Duplicates: 0  Warnings: 0