ODI: Error: ORA-01843: not a valid month

Converting data into TO_DATE format in Oracle database, the correct DATE format is: ‘DD-MON-YY’ and ODI will give an error if format is different, i.e. DD/MM/YYYY, MM/DD/YYYY. Even though the incoming date’s format might be in DD/MM/YYYY or MM/DD/YYYY or etc. you must specify Oracle default date format as ‘DD-MON-YY’.

For sanity check: SELECT TO_DATE('1/30/2012', 'MM/DD/YYYY') from DUAL;  - DUAL is a dummy command for testing purposes.

Notice that even though the date format was specified as 'MM/DD/YYYY', Oracle returned date as ‘DD-MON-YY’ format.

Hope it helped!