One Star

Type Mismatch:cannot convert from String to Date error

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
5 REPLIES
One Star

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

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
One Star

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

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
One Star

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

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.
One Star

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

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
One Star

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

OK, well the good news is we have gotten past the Talend errors and have moved on to the database errors Smiley Wink
I found a relevant thread, but it looks like you need to format your date output to: "yyyy-MM-dd HH:mm:ss"
http://www.talendforge.org/forum/viewtopic.php?id=23292
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.