Solved: convert a Date in string format to a date in a Dat... - Qlik Community

stvn · ‎2019-02-15

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

Anonymous · ‎2019-02-20

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 🙂

View solution in original post

manodwhb · ‎2019-02-15

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

stvn · ‎2019-02-15

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

Anonymous · ‎2019-02-17

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 🙂

stvn · ‎2019-02-18

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

Anonymous · ‎2019-02-18

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 🙂

stvn · ‎2019-02-18

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

manodwhb · ‎2019-02-18

@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.

Anonymous · ‎2019-02-18

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 🙂

Anonymous · ‎2019-02-18

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 🙂

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

Talend Data Integration

v7.x