As part of my job duties, I'm responsible for extracting data from our vendor's Oracle 11g database, and loading it into our SQL Server 2016 database. I've been doing this successfully with SSIS and the Attunity Oracle connectors.
Today I was informed that there was a new column added to the existing Invoices
table on the Oracle side. There was already a DATE column called Order Date
, which contains valid date values with zero'd times, like 2017-12-25 00:00:00
.
The new column is called Order Date Time
and is also a DATE column. When I opened up the SSIS package and pulled up the Oracle source in my DFT, I previewed the data and found the values in Order Date Time
to be 2432-82-75 50:08:01
. I tried converting the column with CAST
and all the TO_*
functions, but the conversions either failed outright, or returned a string of zeros.
TO_CHAR("Order Date Time", 'YYYYMMDDHH24MISS')
yields 00000000000000
After a bit of Googling for "Oracle date value invalid", I'm now thinking that these DATE values are actually corrupted. Am I missing anything here? Is there some sort of special Oracle-specific technique for storing time values in a DATE column that I may not be aware of?
(And yes, it does bother me quite a bit that our vendor added another DATE column instead of just using the time portion of the existing Order Date
column.)
Unfortunately, Oracle database engine allows inserting invalid date values, which leads to many problems especially when importing data to others database engines such as SQL Server.
To handle this issue, you have to implement the logic that fits your needs, as example:
NULL
CASE
statement in your query to replace values withNULL
I faced this issue one time while importing data to SQL Server from an Oracle data source, there was unacceptable date values, i decided to update all records where date are invalid and replace with
NULL
values before starting the import process.There are many links related to this issue: