topic Re: Date Handling in Talend Studio

Date Handling

Anonymous — Sat, 16 Nov 2024 01:39:03 GMT

Hi.

Does anybody have an article or info on how to properly handle dates in Talend Studio? I find that Studio simply will not format dates on the fly to return a consistent date format, causing jobs to fail constantly, and it is really frustrating.

I am building a job that reads in a CSV file which has a date column. Sometimes the data comes to me using the date format dd/MM/yyyy and other times it comes in as dd-MM-yyyy

I just cannot get a standard simple job layout of tfileInputDelimited >> tMap >> tDBOutput layout to handle this.

In SQL, this is dead simple. I just use this: SELECT CONVERT(DATETIME, myDate, 103) and it will handle either date format and set it to a standard format I want. But in Talend Studio I cannot get any TalendDate function to handle both in the same job. I just constantly get errors stating the date cannot be parsed.

I am a SQL developer, not a Java developer.

I'm thinking of just changing the schema to read the source date in as a string into SQL DB table, and then move the data to another table using SQL to convert the string to a date.

Re: Date Handling

Anonymous — Wed, 19 Aug 2020 07:36:54 GMT

In Studio, the component requires to use the right date format to read the date column. To resolve this issue, read the data as a string from CSV file, then check if the string contains "/" or "-" , use corresponding date format to parse the string and convert it to a Date on tMap. eg:

row1.column_name.contains("-")?TalendDate.parseDate("dd-MM-yyyy",row1.column_name):TalendDate.parseDate("dd/MM/yyyy",row1.column_name)

Regards

Shong

Re: Date Handling

Anonymous — Wed, 19 Aug 2020 13:22:46 GMT

Thank you Shong, that worked.

Regards

Steve