Click here to Skip to main content
15,356,336 members
Please Sign up or sign in to vote.
1.00/5 (1 vote)
See more:
Hi,
Switching from MSDAORA1 to OraOLEDB.Oracle for migrating MS SQL 2012 SSIS to 2016. Field is defined as VARCHAR2 on Oracle but has date value. SQL query returns an error:
SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x80040E14.
An OLE DB record is available. Source: "OraOLEDB" Hresult: 0x80040E14 Description: "ORA-01861: literal does not match format string".
An OLE DB record is available. Source: "OraOLEDB" Hresult: 0x80004005 Description: "ORA-01861: literal does not match format string".

What I have tried:

Tried TO_CHAR and TO_DATE but failed with same error. Is there a way to resolve the issue?
Posted
Comments
Dave Kreskowiak 5-Apr-22 18:25pm
   
"Field is defined as VARCHAR2 on Oracle but has date value"
Well, there's a huge mistake in the original database. That NEVER should have been defined as a string.

TO_DATE is the way to go, but since you haven't shown the TO_DATE function you used and a sample of the strings it needs to convert, there's little anyone can say.
Maciej Los 6-Apr-22 1:47am
   
We need details to be able to help you. What is input date format? What have you tried so far (complete example)?
Chris sc Lee 6-Apr-22 17:37pm
   
Tried with following but still no luck:
TO_CHAR(START_DATE) AS TO_CHAR_START_DATE,
TO_CHAR(END_DATE) AS TO_CHAR_END_DATE,
TO_DATE(START_DATE, 'YYYY-MM-DD') AS TO_DATE_START_DATE,
TO_DATE(END_DATE, 'YYYY-MM-DD') AS TO_DATE_END_DATE,

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900