topic Re: Date Conversion in Talend Studio

Date Conversion

dipanjan93 — Mon, 17 Sep 2018 08:33:41 GMT

Hi All,

I have a date(String data type) column in excel file , values are in 2 formats 'dd.MM.yyyy' and 'dd/MM/yyyy'.
I am trying to convert this string type to date using TalendDate.parseDate("dd/MM/yyyy",row2.date).
How am I supposed to use TalendDate.parseDate() for both the date formats within tMap at once?
Right now I am getting Unparseable date format exception since I am unable to handle this using 'dd/MM/yyyy' format. Please refer below for further details -

Exception in component tMap_10
java.lang.RuntimeException: java.text.ParseException: Unparseable date: "21.08.2018"
at routines.TalendDate.parseDate(TalendDate.java:892)
at routines.TalendDate.parseDate(TalendDate.java:836)

Re: Date Conversion

fdenis — Mon, 17 Sep 2018 09:35:19 GMT

excel store date as date format and display it using local configuration.
wat you see in excel is not what Talend see.
if excel think your fiel is a date. he is going to format it a s date an store it as he want.
1-try to read date directly from excel.
2-read as String and print data (tLog) to check the format.
3- try to trim data before formating
good luck

Re: Date Conversion

dipanjan93 — Mon, 17 Sep 2018 10:07:11 GMT

Hi fdenis,

I had already applied all the approaches you have mentioned. Is there no other way to parse the Date column

for both the formats as discussed above i.e., within tMap itself?

Please let me know if you need any additional information from my end.

Thanks in advance!

Re: Date Conversion

fdenis — Mon, 17 Sep 2018 10:30:17 GMT

you can manually format fate using java Split function and retrieving the 3 parts of the date. it may help you to solve your problème.
you may use tjavaRow to complet this task.

Re: Date Conversion

dipanjan93 — Wed, 19 Sep 2018 12:33:52 GMT

Hi fdenis,

I still couldn't figure out the exact process. Could you please explain in a bit illustrative way?

Thanks in advance!

Re: Date Conversion

Anonymous — Wed, 19 Sep 2018 13:16:15 GMT

Hi,

If you are sure that the date values will come only in above two formats, the easiest way will be to replace the character.

Relational.ISNULL(row1.input_date)? null:TalendDate.parseDate("dd/MM/yyyy",row1.input_date.replace(".","/"))

Could you please try this code in tmap and let us know whether it helped to resolve your issue.

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

Warm Regards,

Nikhil Thampi

Re: Date Conversion

dipanjan93 — Wed, 10 Oct 2018 10:40:15 GMT

Hi All,

Is there a way to parse the date from "dd.MM.yy" to "dd/MM/yyyy" ?