This is an old revision of the document!


DATE Data Type - Oracle to SQL Server Migration

Oracle DATE data type stores date and time data (year, month, day, hour, minute and second).

SQL Server DATE data type (available since SQL Server 2008) can store only year, month and day, so to preserve the time part you have to use DATETIME or DATETIME2(0) data types when migrating Oracle DATE.

DATETIME or DATETIME2? Year 1753 Issue

Typically you can map Oracle DATE data type to DATETIME data type in SQL Server since it is available in any SQL Server version.

Why may you need to use DATETIME2(0) that is available since SQL Server 2008 only? The problem is that Oracle DATE range starts from January 1, 4712 BC while SQL Server DATETIME range starts from January 1, 1753 only, and the DATETIME2 range starts from January 1, 0001.

So although DATETIME2 does not fully cover the year range of Oracle DATE, its range is large than SQL Server DATETIME, so mapping from Oracle DATE to SQL Server DATETIME2 can be helpful in some cases.

Consider the following example when we have a table with 0011-07-31 date value in Oracle and want to migrate it to SQL Server:

Oracle:

  CREATE TABLE emp
  (
     name VARCHAR2(30),
     created_dt DATE
  );
 
  INSERT INTO emp VALUES ('John', TO_DATE('0011-07-31', 'YYYY-MM_DD'));
  # 1 row created.
 
  COMMIT;

SQL Server:

  CREATE TABLE emp
  (
     name VARCHAR(30),
     created_dt DATETIME
  );
 
  INSERT INTO emp VALUES ('John', '0011-07-31');
  # Error
  # The conversion of a varchar data type to a datetime data type resulted in an out-of-range value.

But we can insert this date value if we use DATETIME2:

SQL Server:

  DROP TABLE emp;
 
  CREATE TABLE emp
  (
     name VARCHAR(30),
     created_dt DATETIME2(0)
  );
 
  INSERT INTO emp VALUES ('John', '0011-07-31');
  # (1 row(s) affected)

Check the this PL/SQL script to find tables with year 1753 issues.

Resolving Year 1753 Issue in SQLines Data Tool

SQLines Data tool maps Oracle DATE to SQL Server DATETIME by default. So during the table migration you can get “Datetime field overflow” error:

  SCOTT.EMP - Data transfer failed
	[Microsoft][SQL Server Native Client 11.0]Datetime field overflow
     Rows read:     1 (0 rows/sec)
     Rows written:  0 (0 rows/sec, 0 bytes, 0 bytes/sec)
     Transfer time: 109 ms (0 ms read, 0 ms write)

If you know the table that have datetime overflow error, you can check its all DATE columns for minimal values and find the columns that cause the range error:

Oracle:

SELECT MIN(created_dt) FROM emp;
# 0011-07-31

Then you can edit sqlines_cmap.txt configuration file and specify the data type mapping for the specified columns:

-- Column name and data type mapping file
-- schema.table, column, target_column, target_datatype
SCOTT.EMP, CREATED_DT, CREATED_DT, DATETIME2(0)

Now Oracle DATE is mapped to DATETIME2(0) for CREATED_DT column of EMP table and it can be successfully migrated by SQLines Data tool:

SCOTT.EMP - Data transfer complete (session 1)
     Rows read:     1 (0 rows/sec)
     Rows written:  1 (0 rows/sec, 11 bytes, 0 bytes/sec)
     Transfer time: 110 ms (0 ms read, 0 ms write)

If you want to map all columns having DATE data type in Oracle, you can use sqlines_dtmap.txt configuration file to specify the global data type mapping.

See more articles at Oracle to SQL Server Migration Reference.