topic convert a Date in string format to a date in a Date format with another ouput format in Talend Studio

convert a Date in string format to a date in a Date format with another ouput format

stvn — Fri, 15 Feb 2019 11:24:39 GMT

Hi all,

Can any one help me to solve this issue? I have as an output an excel file with a date with this format: 01.05.2015 (dd/MM/yyyy) I'd like to have as an output, a date in a Date type with this format: yyyy-MM-dd'T'HH:mm:ss.SSS'Z'.

This is what I typed in my tMap: (row10.CREATED!=null)?TalendDate.parseDate("yyyy-MM-dd'T'HH:mm:ss.SSS'Z'",row10.CREATED):null and unfortunately it gave me as an ouput: Thu Apr 14 00:00:00 CEST 2016

I tried to use a TalendDate.formatDate but it didn't work.

I'd appreciate any help, thanks in advance

Regards,

Stvn

Re: convert a Date in string format to a date in a Date format with another ouput format

manodwhb — Fri, 15 Feb 2019 11:48:48 GMT

@stvn,you can covert directly by specifying the format for the output field in tmap,please check the below screenshot.

Re: convert a Date in string format to a date in a Date format with another ouput format

stvn — Fri, 15 Feb 2019 16:22:36 GMT

Hi,

the solution you provide did'nt work. do you have another way in mind? my inpput is defined as String

thanks for helping me

Re: convert a Date in string format to a date in a Date format with another ouput format

Anonymous — Sun, 17 Feb 2019 18:16:03 GMT

Hi,

If the input is String, could you please try like below?

Please note that the Time zone is showing as Zulu time zone as per the output format given by you.

The function used in tMap is as below.

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

Warm Regards,
Nikhil Thampi

Please appreciate our Talend community members by giving Kudos for sharing their time for your query. If your query is answered, please mark the topic as resolved 🙂

Re: convert a Date in string format to a date in a Date format with another ouput format

stvn — Mon, 18 Feb 2019 07:13:10 GMT

Hi Mr. Nikhilthampi,

Thank you for your help. I tried what you suggested me and unfortunately I have the error shown below:

You will find on the picture below how I followe your recomendations:

Do you have any suggestions or any tips that could help me?

Warm regards,

Steve

Re: convert a Date in string format to a date in a Date format with another ouput format

Anonymous — Mon, 18 Feb 2019 08:13:14 GMT

Hi Steve,

It seems the input data coming from your source is in the format like "Fri May 01 00:00:00 CEST 2015". But my input date format is in "dd/MM/yyyy".

Could you please change the function in tMap as shown below? It should work fine.

Relational.ISNULL(row1.input_date)?null:
TalendDate.parseDate("EEE MMM dd HH:mm:ss Z yyyy",row1.input_date)

Please refer the screen shot below and it is changing the time zone also accordingly to Zulu time.

You can refer the other formats of date from below link.

https://help.gooddata.com/cloudconnect/manual/date-and-time-format.html

Hope I answered your query. Please spare a second to mark the topic as resolved 🙂

Warm Regards,
Nikhil Thampi

Please appreciate our Talend community members by giving Kudos for sharing their time for your query. If your query is answered, please mark the topic as resolved 🙂

Re: convert a Date in string format to a date in a Date format with another ouput format

stvn — Mon, 18 Feb 2019 12:35:24 GMT

Hi again,

Unfortunately it didn't work. It produces the same error. My input files is an Excel files with a column(Date) format as 21.04.2016. and I set it in talend as String. I don't understand why it says that I have as an input "Fri May 01 00:00:00 CEST 2015" Do you think i should set the input Date as String with a format such as "dd/MM/yyyy"?

May I ask you how you set up your tRowgenerator? I tried and I have as an output a mistakes.

Thank you so much for your help. I'm new on Talend. So I'm still learning.

Warm regards,

Steve

Re: convert a Date in string format to a date in a Date format with another ouput format

manodwhb — Mon, 18 Feb 2019 13:14:03 GMT

@stvn ,I can tell you one thing that your excel file may be corrupted or you may have different date formats in your execle.

since i suggest you to get a single date format for all the records in excel ,will wok the above solutions.

Re: convert a Date in string format to a date in a Date format with another ouput format

Anonymous — Mon, 18 Feb 2019 13:51:08 GMT

Hi,

Excel automatically changes the format of the underlying data even if they are in different format. To verify this issue, could you please save your input file in csv format (not in xls or xlsx format). If you save in this format, it will get rid of all auto formating features.

Now, open the csv file in a simple text editor like, notepad++, textpad etc and see the actual format of the data. Sometimes different rows might be having different date formats also. Once you get more idea about the data, then you can ask the source team to format the date values in a specific format and then parse them accordingly.

At present, you are having input date values in different format. Some are in plain dd/MM/yyyy and some in complex format shown in your error. Could you please verify these details and let us know whether you were able to identify the issue?

Warm Regards,
Nikhil Thampi

Please appreciate our Talend community members by giving Kudos for sharing their time for your query. If your query is answered, please mark the topic as resolved 🙂

Re: convert a Date in string format to a date in a Date format with another ouput format

Anonymous — Mon, 18 Feb 2019 13:54:14 GMT

Hi,

Another query you were asking is the way in which I was generating input data in trowgenerator. Please refer the screen shot for your reference.

Warm Regards,
Nikhil Thampi

Please appreciate our Talend community members by giving Kudos for sharing their time for your query. If your query is answered, please mark the topic as resolved 🙂

Re: convert a Date in string format to a date in a Date format with another ouput format

stvn — Mon, 18 Feb 2019 16:01:14 GMT

Hi again,

I convert the file into csv files and all dates are formated 14.04.2016. I Strutured my Job as shown below:

When I run the Job without the second tMap I can see my dates results. When I add the second tMap where I tried to convert in a dateFormat, It gives me an error. You can see bellow the result from my last running. Dates are formatted in this way:

|31.07.2006
|31.07.2006
|31.07.2006
|31.07.2006

Is there any way to format all dates in a tJavaRow?

Warm Regards,

thank you for your time

Steve

Re: convert a Date in string format to a date in a Date format with another ouput format

stvn — Tue, 19 Feb 2019 07:44:53 GMT

Hi,

You will find as an attachment a copy of my excel sheet in xlsx to test

StringDateToConvert.xlsx

Re: convert a Date in string format to a date in a Date format with another ouput format

stvn — Tue, 19 Feb 2019 07:46:45 GMT

Hi,

@nthampi

@manodwhb

You will find as an attachment a copy of my excel sheet to test. Thank you in advance

StringDateToConvert.xlsx

Re: convert a Date in string format to a date in a Date format with another ouput format

Anonymous — Wed, 20 Feb 2019 05:36:33 GMT

H Steve,

Thanks for the sample file and it really helped to identify the issue. I have used them to transform the data and I was able to print them successfully. The most important change was to read the input data in Date format itself so that we will not face the change in format issue later.

Please refer the screen shots below.

Now, the input schema for excel is as shown below.

tMap screenshot as shown below.

the tMap functions are as shown below.

Relational.ISNULL(row1.DATE)?null:
TalendDate.parseDate("yyyy-MM-dd'T'HH:mm:ss.SSS'Z'",TalendDate.formatDate("yyyy-MM-dd'T'HH:mm:ss.SSS'Z'",row1.DATE)) 

Relational.ISNULL(row1.DATE_2)?null:
TalendDate.parseDate("yyyy-MM-dd'T'HH:mm:ss.SSS'Z'",TalendDate.formatDate("yyyy-MM-dd'T'HH:mm:ss.SSS'Z'",row1.DATE_2))

Warm Regards,
Nikhil Thampi

Please appreciate our Talend community members by giving Kudos for sharing their time for your query. If your query is answered, please mark the topic as resolved 🙂

Re: convert a Date in string format to a date in a Date format with another ouput format

Anonymous — Wed, 20 Feb 2019 08:10:03 GMT

@stvn

Could you please mark the topic as resolved so that it will help other community members also during reference?

Warm Regards,
Nikhil Thampi

Please appreciate our Talend community members by giving Kudos for sharing their time for your query. If your query is answered, please mark the topic as resolved 🙂

Re: convert a Date in string format to a date in a Date format with another ouput format

stvn — Wed, 20 Feb 2019 08:16:38 GMT

Hi,
@nthampi
Your provided solution works pretty well. Again great thanks for your support and your help.
Have a great day.
Warm Regards,
Stevn