Oracle DATE Data with Year Less Than 1753 - Migration to SQL Server

Oracle DATE data type can store dates starting from year 4712 BC while in SQL Server DATETIME data type minimal year is 1753.

Errors in Data

Few databases actually need to store dates before January 1, 1753, but this can happen by a mistake. For example, a user can enter year 0009 instead of 2009.

To resolve this issue, you can use DATETIME2 data type that supports dates starting from January 01, 0001 or correct data in Oracle before migration if this is an error in data.

The following PL/SQL script can be used to find DATE columns containing data with year less than 1753 in the specified schema (change schema SCOTT to your schema before execution):

   SET SERVEROUTPUT ON
 
   DECLARE 
 
   CURSOR c1 IS
     SELECT OWNER , TABLE_NAME, COLUMN_NAME
     FROM DBA_TAB_COLUMNS 
     WHERE DATA_TYPE = 'DATE' AND OWNER IN ('SCOTT') AND TABLE_NAME NOT LIKE 'BIN$%';
 
    stmt VARCHAR2(300);
    cnt NUMBER := 0;
    total_found NUMBER := 0;
    total_columns NUMBER := 0;
 
   BEGIN
 
     FOR r1 IN c1
     LOOP
 
      stmt := 'SELECT COUNT(*) FROM ' || r1.OWNER || '.' || r1.TABLE_NAME || 
                  ' WHERE EXTRACT(YEAR FROM ' || r1.COLUMN_NAME || ') < 1753';   
 
      BEGIN
 
       EXECUTE IMMEDIATE stmt INTO cnt;
 
       IF cnt > 0 THEN
        DBMS_OUTPUT.PUT_LINE('Table:' || r1.OWNER || '.' || r1.TABLE_NAME || ' Column: ' || r1.COLUMN_NAME);
        total_found := total_found + 1;
       END IF;
      EXCEPTION WHEN OTHERS THEN
        DBMS_OUTPUT.PUT_LINE('Failed: ' || r1.OWNER || '.' || r1.TABLE_NAME || '.' || r1.COLUMN_NAME);
      END; 
 
     total_columns := total_columns + 1;
 
     END LOOP;
 
     DBMS_OUTPUT.PUT_LINE(total_found || ' columns found'); 
     DBMS_OUTPUT.PUT_LINE(total_columns || ' columns checked'); 
 
   END;
   /

Sample Output:

 Table:CFG.TICKETS Column: LOADING
 Table:CFG.WEIGHTS Column: IN_DATE
 Table:CFG.AUDIT Column: OUT_DATE

 3 columns found
 335 columns checked

• Oracle White Papers, Articles and Reference