How to convert a String to a Date in tMap

[Anonymous]

Hello,

I have an excel file with a date column. 

The column is defined in excel as a date with a format of '*3/12/2018'. I  have changed my schema in talend for this column to be a string.

The column can contain nulls in excel.

I have tried so many options an continuously get errors. 

I want to convert the date string (with mm/dd/yyyy) to the same format but as a date for the output Postgres table.

I am using the following expression:

(StringHandling.LEN(row1.open_item_date) <1)? null :
TalendDate.parseDateLocale("EEE MMM DD HH:mm:ss zzz yyyy", row1.open_item_date,"EN");

This returns: "2017-01-20 00:00:00.000000 -06:00:00"

When i use anything else i get an error.  Could you please help as all i want is the mm/dd/yyyy format as my output.

[Anonymous]

Hi,

    Please refer the below details to convert the data to correct date. I am generating a test string as mentioned in your post "*3/12/2018". First I am doing the cleansing activity using java function and after that I am formatting it to right way.

The code to be used is as shown below.

Relational.ISNULL(row1.input)?null:(TalendDate.parseDate("MM/dd/yyyy",row1.input.replaceAll("\\*", "0"))) 

If the details has helped you, could you please mark the topic as resolved? Kudos are also welcome 🙂

Warm Regards,

Nikhil Thampi