Analytics Software

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Unable to convert varchar to date

2717688Nov 13 2014 — edited Nov 17 2014

I'm trying to convert a date which appears in a VARCHAR2(240) datatype to DATE datatype.The date in the source data base appears in the following way: yyyy/mm/dd HH24:MI:SS. For example: 2007/12/31 00:00:00.

I've tried to use in any of the following formulas in the ODI:

1) TO_DATE(ATTRIBUTE7,'yyyy/mm/dd')

2) TO_CHAR(to_date(ATTRIBUTE7,'YYYY/MM/DD HH24:MI:SS'),'YYYY/MM/DD')

3) TRUNC(TO_DATE(ATTRIBUTE7,’YYYY/MM/DD H24:MI:SS’))

4) TRUNC(fnd_date.canonical_to_date(ATTRIBUTE7))

But none of them worked.Every time i'm getting the following error:

ODI-1227: Task SrcSet0 (Loading) fails on the source ORACLE connection DEV.

Caused By: java.sql.SQLDataException: ORA-01861: literal does not match format string

I'll be happy for some help...

Thanks

Yaron

Locked Post

New comments cannot be posted to this locked post.

Locked on Dec 15 2014

Added on Nov 13 2014

#business-intelligence-applications, #data-integrator, #system-management-and-integration

9 comments

3,230 views