Detecting Invalid Date Data in Oracle and MySQL Using Error Logs and Temporary Tables

Background

During data migration or upgrading a database to a newer version, loosely configured parameters in older versions can allow insertion of malformed date strings, which later cause errors. The article presents practical ways to detect such invalid date data in Oracle and MySQL before they cause failures.

Oracle Validation Method

2.1 Create a test table and insert sample data

CREATE TABLE T1(ID NUMBER, CREATE_DATE VARCHAR2(20));
INSERT INTO T1 SELECT 1, '2007-01-01' FROM DUAL;
INSERT INTO T1 SELECT 2, '2007-99-01' FROM DUAL;   -- abnormal data
INSERT INTO T1 SELECT 3, '2007-12-31' FROM DUAL;
INSERT INTO T1 SELECT 4, '2007-12-99' FROM DUAL;   -- abnormal data
INSERT INTO T1 SELECT 5, '2005-12-29 03:-1:119' FROM DUAL;   -- abnormal data
INSERT INTO T1 SELECT 6, '2015-12-29 00:-1:49' FROM DUAL;   -- abnormal data

2.2 Create an error‑log table

Oracle provides the package DBMS_ERRLOG.CREATE_ERROR_LOG to record errors generated by DML statements.

EXEC DBMS_ERRLOG.CREATE_ERROR_LOG('T1','T1_ERROR','DEMO');

2.3 Insert data into a temporary table and validate dates

-- Create temporary table for validation
CREATE TABLE T1_TMP(ID NUMBER, CREATE_DATE DATE);

-- Insert data, converting strings to DATE; log errors to T1_ERROR
INSERT INTO T1_TMP
SELECT ID, TO_DATE(CREATE_DATE, 'YYYY-MM-DD HH24:MI:SS')
FROM T1 LOG ERRORS INTO T1_ERROR REJECT LIMIT UNLIMITED;

2.4 Query the error log

SELECT * FROM DEMO.T1_ERROR;

The ID column identifies the rows containing invalid dates.

MySQL Validation Method

3.1 Create a test table with lax SQL_MODE

-- Create test table
CREATE TABLE T_ORDER(
    ID BIGINT AUTO_INCREMENT PRIMARY KEY,
    ORDER_NAME VARCHAR(64),
    ORDER_TIME DATETIME);

-- Allow invalid dates
SET SQL_MODE='STRICT_TRANS_TABLES,ALLOW_INVALID_DATES';

INSERT INTO T_ORDER(ORDER_NAME,ORDER_TIME) VALUES
    ('MySQL','2022-01-01'),
    ('Oracle','2022-02-30'),
    ('Redis','9999-00-04'),
    ('MongoDB','0000-03-00');

SELECT * FROM T_ORDER;

3.2 Create a temporary table for validation

-- Temporary table with only the columns to be checked
CREATE TABLE T_ORDER_CHECK(
    ID BIGINT AUTO_INCREMENT PRIMARY KEY,
    ORDER_TIME DATETIME);

-- Use the default strict SQL_MODE of newer MySQL versions
SET SQL_MODE='ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION';

INSERT IGNORE INTO T_ORDER_CHECK(ID, ORDER_TIME)
SELECT ID, ORDER_TIME FROM T_ORDER;

3.3 Compare original and temporary tables

SELECT
    T.ID,
    T.ORDER_TIME AS ORDER_TIME,
    TC.ORDER_TIME AS ORDER_TIME_TMP
FROM T_ORDER T
INNER JOIN T_ORDER_CHECK TC ON T.ID = TC.ID
WHERE T.ORDER_TIME <> TC.ORDER_TIME;

The result lists rows where the original date differs from the validated one.

Shortcut Method

For quick checks, regular‑expression matching can be used, though it becomes complex for full date validation.

-- Oracle example
SELECT * FROM T1 WHERE NOT REGEXP_LIKE(CREATE_DATE,'^((?:19|20)\d\d)-(0[1-9]|1[012])-(0[1-9]|[12][0-9]|3[01])$');

The query returns rows with malformed dates.