Oracle DATE vs SQL DATE

During the development of a simple (or so we thought Wink) SSIS task a strange error came up: we had a dbtimestamp column from Oracle which we had to load into a SQL Server (2005!) table. It was a transaction date so by default there shouldn't have been any problem with it. By default.

But life's different Smile: the DATA FLOW task's OLE DB Destionation always became red with the following error message:

SSIS Error Code DTS_E_OLEDBERROR.  An OLE DB error has occurred. Error code: 0x80004005. An OLE DB record is available.  Source: "Microsoft SQL Native Client"  Hresult: 0x80004005  Description: "Invalid character value for cast specification".

There was an error with input column "..." (39780) on input "OLE DB Destination Input" (4241). The column status returned was: "Conversion failed because the data value overflowed the specified type.". 

We checked the source table and found out that six of the values have the 0208.02.04 instead of 2008.02.04. Next step was checking the column data type in both databases. They were the same dbtimestamp. As far as we know dates earlier than 1753 cannot be inserted into these kind of columns but after a little googling it turned out that Oracle's date type can contain dates from "from January 1, 4712 BC to December 31, 9999 AD."

Here you can find the exact specification.

Since we were not authorized to modify the source data another solution had to be found:

  • we inserted a conditional split (Year([Date column]) < 1800),
  • of course these 0208 values were not valid as Year values so those rows went to the error thread, 
  • the dates newer than 1800 were transfered smoothly,
  • and for the wrong ones we didn't use the Conditional split's other output put redirected the false rows, replaced the wrong dates with NULLs, multicasted those rows and loaded them into an ErrorLog table and on the other hand into the fact table with NULLs instead of dates.
I'm sure there are more elegant ways to achieve this but at the moment we found out this and implemented it immediately. If I'm right SQL Server 2008 can handle dates earlier than 1753.
Published Tuesday, March 31, 2009 12:52 PM by MartinIsti

Comments

# Twitter Trackbacks for Oracle DATE vs SQL DATE - MartinIsti MS BI blog [sqlblogcasts.com] on Topsy.com

Pingback from  Twitter Trackbacks for                 Oracle DATE vs SQL DATE - MartinIsti MS BI blog         [sqlblogcasts.com]        on Topsy.com

# Julienne Vegetable peeler

Sunday, September 7, 2014 3:01 AM by Julienne Vegetable peeler

Oracle DATE vs SQL DATE - MartinIsti MS BI blog

Powered by Community Server (Commercial Edition), by Telligent Systems