Error message: ORA-01861: literal does not match format stng

[hargun]

HI,

i am reading columns CLOSE_DT AS DATE datatype from source(db2 stage) and inserting into oracle DB using oracle connector stage having datatype as DATE.

The job design is as follows


db2 stage....................tranformer.......................oracle connector stage.


i have tried by using

Code: Select all

StringToDate(select_stats.CLOSE_DT,"%yyyy-%mm-%dd") 

but getting error

XFM_data,1: Data string '08/20/2013' does not match format '%yyyy-%mm-%dd': the value for tag %yyyy has fewer characters than expected.

however i tried by using

Code: Select all

StringToDate(select_stats.CLOSE_DT,"%mm/%dd/%yyyy") 

to avoid the error and error also gone but getting the below error now.

Error message: ORA-01861: literal does not match format string from the oracle stage.



The date from source coming as '08/20/2013' as char.

please help me on this.

[chulett]

You have an Oracle issue, not a DataStage one per se. What does the SQL look like that the Oracle Connector is using, is there a TO_DATE() function in it? If so, what format string is it using? A mismatch there is what throws that Oracle error.

[arunkumarmm]

however i tried by using

Code: Select all

StringToDate(select_stats.CLOSE_DT,"%mm/%dd/%yyyy") 

to avoid the error and error also gone but getting the below error now.

Error message: ORA-01861: literal does not match format string from the oracle stage.



The date from source coming as '08/20/2013' as char.

Craig answered this. But yeah, your date format in the data is not matching the date format specified in your target SQL. You should change your data to match that format or you should change your query to expect in the format what you have in your data.

[abhinavagarwal]

Hi .... Basically your target table has different date format with respect to your input data. So you have to change your date in the desired format and then try to load.
Hope this will help.
Cheers
Abhinav