2

I am using SSIS to convert some oracle data to sql server. I found for Oracle date timestamp6 like this 

26-DEC-82 12.00.00.000000000 AM

will cause the conversion in SSIS to fail

Error: Year, Month, and Day parameters describe an un-representable

I think it's because ssis don't know whether it's 2082 or 1982, so don't know how to convert.How can I convert the oracle dates to something with yyyy for the year part?

update: tried to_char function mentioned by Hadi. I can see the year now is 2682 (most of them). I added a pic showing with to_char and original column for plate_date and sold_date columns. As you can see most of the years are 26xx, two exceptions are 18xx. Can someone explain?

enter image description here

thotwielder
  • 1,563
  • 7
  • 44
  • 83
  • Well, do *you* know whether it is 1982 or 2082? Try RR format mask instead of YY (on Oracle side; I don't speak SQL Server). – Littlefoot Apr 18 '19 at 10:28
  • Sorry I really don't know much about Oracle. Why Oracle allow this format if oracle don't know it's 1982 or 2082? And what's RR format? – thotwielder Apr 18 '19 at 11:05
  • 1
    [See this](https://docs.oracle.com/en/database/oracle/oracle-database/12.2/sqlrf/Format-Models.html#GUID-49B32A81-0904-433E-B7FE-51606672183A). Looks like you need to change the way you're passing the data; preferably as an actual date/timestamp, but if it must be a string then specify the output format from Oracle, using a 4-digit year, month numbers etc. – Alex Poole Apr 18 '19 at 11:17
  • [This shows how to get a 'safe' format](https://stackoverflow.com/a/44035953/266304), but you might need something specific for SSIS (if you can't tell that what format to expect). – Alex Poole Apr 18 '19 at 11:42
  • @AlexPoole so it depends on the clients to display the value? but my question is the client will never know whether it's 2082 or 1982 (although it most likely a 1982). Does oracle know internally? If yes can it somehow convert it to a string in yyyy format for the year part so the client will know what to do. – thotwielder Apr 18 '19 at 12:53
  • The client is displaying the timestamp as a string, using either its own settings or the session NLS settings. It doesn't need to know what that string means. Oracle uses its own internal mechanism so it does know the year; your client is just throwing away the full year info when it formats. (Those default formats predate Y2K...). See my earlier comments for how to get the date displayed as a string in a specific format, with full year info. – Alex Poole Apr 18 '19 at 15:12

2 Answers2

0

In the Oracle Source use an SQL Command to convert TimeStamp to nvarchar using TO_CHAR() function and use the universal data format yyyy-MM-dd HH:mm:ss:

TO_CHAR(SOURCECOLUMN, 'YYYY-MM-DD HH24:MI:SS')

And in SSIS data flow add a derived column with the following expression:

(DT_DATE)[SOURCECOLUMN]

Or add a data conversion transformation and convert the column to date data type.

Hadi
  • 36,233
  • 13
  • 65
  • 124
0

In SQL Server, the datatype "timestamp" is nothing to do with dates or times.

Microsoft have renamed their old "timestamp" datatype to "rowversion" because it is just an 8-byte number that is used to record a sequence of “row changed” events.

On the other hand Oracle's "timestamp" really is about time because Oracle's "timestamp" extends their "date" datatype. more here.

Unfortunately, SQL Server still recognises "timestamp" as a valid datatype name.

So, I suspect that your error message may have something to do with the timestamp-timestamp homonym.

Ken Evans
  • 362
  • 1
  • 11