Date Conversion

[dipanjan93]

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)

[fdenis]

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

[dipanjan93]

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!

[fdenis]

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.

[dipanjan93]

Hi fdenis,

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

Thanks in advance!

[Anonymous · Accepted solution]

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

[dipanjan93]

Hi All,

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