Why am I seeing values of '2432-82-75 50:08:01' in Oracle DATE column?

Question

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.)

Answer 1

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:

• You can exclude these records from you queries by filtering on acceptable date ranges: (WHERE date between ...)
• You can Update records with invalid values by replacing with NULL
• You can use a CASE statement in your query to replace values with NULL

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:

• Detecting invalid values in the DB
• How to identify invalid (corrupted) values stored in Oracle DATE columns
• Corrupt date fields causing query failure in Oracle
• Invalid Date in DATE Column SQLPlus VS SQLDeveloper
• Ask Tom - date validation in oracle
• Dealing with invalid dates
• Error: Invalid date format
• DB Connect; Oracle DB date field data is corrupt