Oracle to SQL2005 DATETIME field overflows in SSIS

Question

I'm importing data from Oracle to SQL Server 2005 using SSIS. I think the datetime fields in Oracle are stored to a higher precision than SQL Server 2005 "DATETIME" fields will allow, causing overflow errors. What's the best practice for getting the table in?

These tables have lots of datetime fields, and there are lots of tables, so it'd be awesome if there wasn't a ton of manual work for each field...

I've read https://stackoverflow.com/questions/2231028/dealing-with-timestamp-datetime-when-copying-data-from-oracle-to-sql-server-using and that could work, but I'd have to do that for each field, for every table, and I'd rather have a universal setting or process...

Any ideas?

Answer 1

The issue in this is that SQL Server is limited in what dates are valid (January 1, 1753, through December 31, 9999), while Oracle dates from January 1, 4712 BCE through December 31, 9999. So any date in Oracle less than January 1, 1753 will overflow the datetime sql server datatype.

If you move up to Sql 2008 you can use Datetime2 type to help with this issue as that has valid dates from 0001-01-01 through 9999-12-31.

See details here: Sql Server Date and Time types

And here: Oracle Date Type

Answer 2

Just flagging this as 'answered' since there appears to be no global setting.

Answer 3

I will suggest to use the following instruction in your SELECT statement:

CASE WHEN date_birth < TO_DATE('01-01-1753','DD-MM-YYYY') THEN NULL
ELSE to_char(date_birth, 'YYYY-MM-DD HH24:MI:SS') END AS date_birth

Answer 4

There is another cause. Check to make sure that the dates are valid. We are in the middle of this, and we have years specified as something like 5096, which apparently cannot be moved to SQL server... same goes if you have a year of 0207 instead of 2007.