DATE Data Type - Oracle to SQL Server Migration

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

Oracle DATE Internals

A DATE value requires 7 bytes in Oracle:

Byte 1 Byte 2 Byte 3 Byte 4 Byte 5 Byte 6 Byte 7
Century + 100 MOD(Year/100) + 100 Month Day Hour + 1 Minute + 1 Second + 1

The first byte stores the first 2 digits of the year (century) plus 100 . The second byte stores the last 2 digits of the year plus 100.

The third and fourth bytes store the month and day, respectively.

The fifth, sixth and seventh bytes store hour, minute and second, but all these values are incremented by 1.

For example, the DATE value '2012-09-17 13:31:01' is internally stored as:

Byte 1 Byte 2 Byte 3 Byte 4 Byte 5 Byte 6 Byte 7
120 112 09 17 14 32 02

Oracle OCI returns the DATE data in the internal 7-byte format if you bind the column (OCIDefineByPos i.e.) using SQLT_DAT data type code.

Resources

SQLines Services

SQLines offers database administration, optimization and migration services for Oracle and SQL Server databases and applications. For more information, please Contact Us.

You could leave a comment if you were logged in.