search

dmtolpeko / sqlines

SQLines Open Source Database Migration Tools
http://www.sqlines.com
Apache License 2.0
391 stars 174 forks source link

Oracle to MSSQL 2016: failed to transfer dates before 01-01-1753 #7

Open ChaosBladeCoder opened 6 years ago

ChaosBladeCoder commented 6 years ago

I'm trying to migrate an entire Oracle database to SQL Server, and I'm running into several issues. I will log them separately here.

I'm trying to transfer an Oracle table with a DATE type column, which contains several records with dates before 01-01-1753. Once SQLines encounters the first such record it fails this table with the error "[Microsoft][SQL Server Native Client 11.0]Datetime field overflow".

This happens because SQLines converted the Oracle "DATE" type column to MSSQL type "datetime", which does not support dates before January 1, 1753. [See https://docs.microsoft.com/en-us/sql/t-sql/data-types/datetime-transact-sql].

SQLines should instead, whenever possible, convert such columns to type "datetime2", which supports the full range of dates from 0001-01-01 through 9999-12-31. [See https://docs.microsoft.com/en-us/sql/t-sql/data-types/datetime2-transact-sql]. This datatype is supported from MSSQL version 2008 and up.

ChaosBladeCoder commented 6 years ago

I should probably note that this situation is not as uncommon as you might think. In my database 16 out of 290 tables failed with this error. Mostly this happens because users make typo's like "204" when trying to type "2004" when entering dates.