as alternate i have tried to convert in the oracle SQL and used the below function

 

TO_CHAR (TO_DATE(a.APP_DATE ,'yyyymmdd'),'ddmmyyyy')as approved_date

when i tried to load through custom sql, its throwing below error

The problem relates to the the format of the field vs what you are trying to do with it... Is app_date already a date? if so, you don't need TO_DATE()

For example this works > select TO_CHAR(sysdate,'dd-MON-yyyy') from dual

There is some useful stuff in this thread > ORA-01841 :(full) year must be between -4713 an... | Oracle Community

And here > Oracle/PLSQL Date Functions

Cheers

Mark

if app_date is a CHAR, do you need to swap it to a date? or leave it as CHAR?

I ask because at the moment, app_Date CHAR, you swap to DATE, then back to CHAR?!

TO_CHAR (TO_DATE(a.APP_DATE ,'yyyymmdd'),'ddmmyyyy') as approved_date

I substituted app_date for a string (today's date), all the below work

select TO_CHAR (TO_DATE('04/02/2016' ,'DD-MM-YYYY'),'DD-MM-YYYY') as approved_date from dual;          -- leaves as CHAR, after swapping to date

select TO_DATE('04/02/2016' ,'DD-MM-YYYY') as approved_date from dual;                                                       -- swaps CHAR to DATE

select TO_DATE(TO_CHAR (TO_DATE('04/02/2016' ,'DD-MM-YYYY'),'DD-MM-YYYY'),'DD-MM-YYYY') as approved_date from dual;      -- swaps CHAR to DATE THEN CHAR then DATE!

The main thing is the format of the 'date', if you swap my working one examples from DD-MM-YYYY to DDMMYYYY they fail (they don't match the input string).

IF appdate was formatted as 04022016 then DDMMYYYY would work > select TO_DATE('04022016' ,'DDMMYYYY') as approved_date from dual;

Cheers

Mark

Great you got something working!

I think you are close, its just about keeping any eye on the format of the input (the appt date) vs how you reference (the appt date format) in the SQL, they need to match.

If you need any further help, let me know

Cheers
Mark