Casting date in Talend Data Integration

Question

In a data flow from one table to another, I would like to cast a date.

The date leaves the source table as a string in this format: "2009-01-05 00:00:00:000 + 01:00".

I tried to convert this to a date using a tConvertType, but that is not allowed apparently. My second option is to cast this string to a date using a formula in a tMap component.

At the moment I tried these formulas:

- TalendDate.formatDate("yyyy-MM-dd",row3.rafw_dz_begi);
- TalendDate.formatDate("yyyy-MM-dd HH:mm:ss",row3.rafw_dz_begi);
- return TalendDate.formatDate("yyyy-MM-dd HH:mm:ss",row3.rafw_dz_begi);

None of these worked. When inserting the result into the target-table (MySQL, InnoDB) a receive the error message that the date is not valid. The format of the target field is a MySQL Date field.

How can I cast the date to the desired format?

Solution 1

Talend offers you a nice way of handling date formats. You can easily change the date format in the Schema editor tab at the tMap window.

It works for both tMap input and output flows. I've added a picture for a better illustration.

Solution 2

to cast this string to a date using a formula.... error message that the date is not valid. The format of the target field is a MySQL Date field.

What I understand from your question is, you want to insert a date into MySQL Date field.

But the method that you are using, returns the 'String' type.

TalendDate.formatDate(String pattern, Date date);    //formats a date into Date/Time string

So in that case, if your field is of 'Date' type.

TalendDate.parseDate("yyyy-MM-dd", TalendDate.formatDate("yyyy-MM-dd",row3.rafw_dz_begi));