topic Re: Type Mismatch:cannot convert from String to Date error in Talend Studio

Type Mismatch:cannot convert from String to Date error

Anonymous — Tue, 01 May 2012 15:33:01 GMT

Hi,
I am getting the following error for the tmap configuration attached. Can anyone please advice me on how to go about this?
Many Thanks,
Manju

Re: Type Mismatch:cannot convert from String to Date error

Anonymous — Tue, 01 May 2012 16:42:29 GMT

Manju, it is a bit tricky because the date has backslashes instead of forward slashes. This means you need to escape the backslashes when parsing the date.
You can use:
TalendDate.parseDate("MM\\dd\\yyyy",StringHandling.CHANGE(row1.Column4,"d",""))
and then format the output field as "MMddyyyy" in the tMap schema

Re: Type Mismatch:cannot convert from String to Date error

Anonymous — Tue, 01 May 2012 17:09:54 GMT

Hi,
Thanks for your reply. There are no back slashes in the data.
The input data looks like this , ""d20120327""
So inorder to remove the extra "" i am using this, (StringHandling.CHANGE(row1.Column4,"\"","")
and then i need to remove d , so i am using StringHandling.CHANGE(StringHandling.CHANGE(row1.Column4,"\"",""),"d","")
So the actual string which i need to convert is "20120327"

In the expression editor , if i only provide
TalendDate.parseDate("YYYYMMDD",StringHandling.CHANGE(StringHandling.CHANGE(row1.Column4,"\"",""),"d",""))
i get ORA-00984: column not allowed here in the console.
if i give this
TalendDate.formatDate("yyyymmdd", TalendDate.parseDate("YYYYMMDD",StringHandling.CHANGE(StringHandling.CHANGE(row1.Column4,"\"",""),"d","")))
i get the type mismatch error.

Please guide me on this.

Many Thanks,
Manju

Re: Type Mismatch:cannot convert from String to Date error

Anonymous — Tue, 01 May 2012 17:21:32 GMT

You can get rid of the TalendDate.formatDate because formatDate returns a string.
Enter this in the field
TalendDate.parseDate("yyyyMMdd",StringHandling.CHANGE(StringHandling.CHANGE(row1.Column4,"d",""),"\"",""))
and do the formatting in the schema on the tMap out.

Re: Type Mismatch:cannot convert from String to Date error

Anonymous — Tue, 01 May 2012 17:29:36 GMT

Thanks.
When i do that, i am getting this error ,
ORA-00984: column not allowed here
Is it becoz the column in not nullable in DB?
In the tmap out, i have the column type as Date and format as "yyyymmdd"
Am i still missing something?

Many Thanks,
Manju

Re: Type Mismatch:cannot convert from String to Date error

Anonymous — Tue, 01 May 2012 18:07:25 GMT

OK, well the good news is we have gotten past the Talend errors and have moved on to the database errors
I found a relevant thread, but it looks like you need to format your date output to: "yyyy-MM-dd HH:mm:ss"
https://community.talend.com/t5/Design-and-Development/resolved-Always-get-ora-00984-error-when-executing/td-p/58924
Either that or keep the output data type a string and use the following format in the expression:
TalendDate.formatDate("MM/dd/yyyy",TalendDate.parseDate("yyyyMMdd",StringHandling.CHANGE(StringHandling.CHANGE(row1.Column4,"d",""),"\"",""))
Curious to see which solution works.