topic Re: How to convert a special date in Talend Studio

How to convert a special date

Anonymous — Sun, 23 Apr 2017 21:18:14 GMT

Hello ,

I need to convert this kind a date below in date format please .

My code below isn't working .

Can you help me please

TalendDate.formatDate("yyyy-MM-dd", TalendDate.parseDate("MMM dd';' yyyy HH:mm:ss", row1.newColumn1 )

the date to convert .(date in the row1.newColumn1)

Dec 1; 2016 00:00:00

Best regards

Capsi

Re: How to convert a special date

Anonymous — Sun, 23 Apr 2017 21:20:50 GMT

Sorry ,

find below the date to convert .(date in the row1.newColumn1)

Dec 1; 2016 00:00:00

Re: How to convert a special date

cterenzi — Mon, 24 Apr 2017 16:38:29 GMT

Your formula works for me in a tMap (after correcting the missing ')' at the end). Where are you trying to use the formula and what error are you receiving?

Re: How to convert a special date

Anonymous — Mon, 24 Apr 2017 17:31:54 GMT

Hello,

I'm trying to use the formula in a "constructeur d'expression of a tmap "

I don't understand what parenthis missing .

TalendDate.formatDate("yyyy-MM-dd", TalendDate.parseDate("MMM dd; yyyy HH:mm:ss", row1.newColumn1 ))

below the error .

Thanks a lot

Démarrage du job secondJob a 18:31 24/04/2017.
[statistics] connecting to socket on port 3458
[statistics] connected
Exception in component tMap_1
java.lang.RuntimeException: java.text.ParseException: Unparseable date: "Begin date"
at routines.TalendDate.parseDate(TalendDate.java:895)
at routines.TalendDate.parseDate(TalendDate.java:839)
at local_project.secondjob_0_1.secondJob.tFileInputDelimited_1Process(secondJob.java:1046)
at local_project.secondjob_0_1.secondJob.runJobInTOS(secondJob.java:1484)
at local_project.secondjob_0_1.secondJob.main(secondJob.java:1341)
[statistics] disconnected
Caused by: java.text.ParseException: Unparseable date: "Begin date"
at java.text.DateFormat.parse(Unknown Source)
at routines.TalendDate.parseDate(TalendDate.java:881)
... 4 more

Re: How to convert a special date

cterenzi — Mon, 24 Apr 2017 19:16:31 GMT

That error means "Begin date" appears in your data where Talend is expecting a date. Perhaps your input data has a header record which needs to be skipped?

Re: How to convert a special date

Anonymous — Mon, 24 Apr 2017 19:28:03 GMT

Sorry ,

Can you tell me how to convert this date (Dec 1; 2016 00:00:00) to (2016/12/01 00:00:00) in talend please ?

Which code to pout in the tmap please ?

Re: How to convert a special date

cterenzi — Mon, 24 Apr 2017 19:55:44 GMT

TalendDate.formatDate("yyyy/MM/dd HH:mm:ss", TalendDate.parseDate("MMM dd';' yyyy HH:mm:ss", row1.newColumn1 ))

The error you posted above is occurring because at least one row in row1.newColumn1 has a value of "Begin Date" which can't be parsed as a date.

Re: How to convert a special date

Anonymous — Mon, 24 Apr 2017 20:34:15 GMT

The error you talking about was because i get a header in my file to load .

after modifying the configuration of my composant tfileInputDelimited_1 I get the following error with the code you sent .

Do you have any idea please ?

Démarrage du job secondJob a 21:31 24/04/2017.
[statistics] connecting to socket on port 3945
[statistics] connected
Exception in component tMap_1
java.lang.RuntimeException: java.text.ParseException: Unparseable date: "Dec 1; 2016 00:00:00"
at routines.TalendDate.parseDate(TalendDate.java:895)
at routines.TalendDate.parseDate(TalendDate.java:839)
at local_project.secondjob_0_1.secondJob.tFileInputDelimited_1Process(secondJob.java:1277)
at local_project.secondjob_0_1.secondJob.runJobInTOS(secondJob.java:1717)
at local_project.secondjob_0_1.secondJob.main(secondJob.java:1574)
Caused by: java.text.ParseException: Unparseable date: "Dec 1; 2016 00:00:00"
at java.text.DateFormat.parse(Unknown Source)
at routines.TalendDate.parseDate(TalendDate.java:881)
... 4 more
[statistics] disconnected
Job secondJob terminé à 21:31 24/04/2017. [Code sortie=1]

Re: How to convert a special date

cterenzi — Mon, 24 Apr 2017 20:47:15 GMT

You'll need to provide additional information to solve the issue then. Here are my tMap expression and my job output. In the job output, col1 is the input string and col2 is the result of the expression.

[statistics] connecting to socket on port 3814
[statistics] connected
Dec 1; 2016 00:00:00|2016/12/01 00:00:00
Dec 1; 2016 00:00:00|2016/12/01 00:00:00
Dec 2; 2016 00:00:00|2016/12/02 00:00:00
Dec 3; 2016 00:00:00|2016/12/03 00:00:00
Dec 4; 2017 00:00:00|2017/12/04 00:00:00
[statistics] disconnected
Job test ended at 15:40 24/04/2017. [exit code=0]

Re: How to convert a special date

Anonymous — Mon, 24 Apr 2017 20:59:50 GMT

OK.please find below the additionnal informations

Below the content of the file what i'm trying to load and a screenshot of my tmap.

Hope it will clear for you to help me .

Thanks

TT,Begin date,End date,Watched,Impulsive,Conditional
10,Dec 1; 2016 00:00:00,Dec 1; 2036 23:59:56,N/A,N/A,NO
18,Dec 1; 2016 00:00:00,Dec 1; 2036 23:59:56,N/A,N/A,NO
19,Dec 1; 2016 00:00:00,Dec 1; 2036 23:59:56,N/A,N/A,NO
32,Dec 1; 2016 00:00:00,Dec 1; 2036 23:59:56,N/A,N/A,NO
41,Dec 1; 2016 00:00:00,Dec 1; 2036 23:59:56,N/A,N/A,NO
42,Dec 1; 2016 00:00:00,Dec 1; 2036 23:59:56,N/A,N/A,NO
50,Dec 1; 2016 00:00:00,Dec 1; 2036 23:59:56,N/A,N/A,NO
63,Dec 1; 2016 00:00:00,Dec 1; 2036 23:59:56,N/A,N/A,NO
68,Dec 1; 2016 00:00:00,Dec 1; 2036 23:59:56,N/A,N/A,NO

Re: How to convert a special date

cterenzi — Mon, 24 Apr 2017 21:17:43 GMT

While I would not have used a tMap variable in this case--instead I put the date expression directly in the output column--I recreated your tMap and still don't receive an error.

Best I can come up with is that there's a language or locale issue here. If your system is set to use a French locale, it may be expecting "déc." when parsing "MMM" and rejecting "dec" as invalid. I don't have experience with non-English locales, unfortunately.

Re: How to convert a special date

Anonymous — Mon, 24 Apr 2017 21:35:05 GMT

It's working !!!!!!, your are just a master .

After you last post about the locale settings , I change my code and it's working fine .

Please see below my code and the output and thank you so much .

10,2016/12/01 00:00:00,Dec 1; 2036 23:59:56,N/A,N/A,NO
18,2016/12/01 00:00:00,Dec 1; 2036 23:59:56,N/A,N/A,NO
19,2016/12/01 00:00:00,Dec 1; 2036 23:59:56,N/A,N/A,NO
32,2016/12/01 00:00:00,Dec 1; 2036 23:59:56,N/A,N/A,NO
41,2016/12/01 00:00:00,Dec 1; 2036 23:59:56,N/A,N/A,NO
42,2016/12/01 00:00:00,Dec 1; 2036 23:59:56,N/A,N/A,NO
50,2016/12/01 00:00:00,Dec 1; 2036 23:59:56,N/A,N/A,NO
63,2016/12/01 00:00:00,Dec 1; 2036 23:59:56,N/A,N/A,NO
68,2016/12/01 00:00:00,Dec 1; 2036 23:59:56,N/A,N/A,NO
69,2016/12/01 00:00:00,Dec 1; 2036 23:59:56,N/A,N/A,NO
70,2016/12/01 00:00:00,Dec 1; 2036 23:59:56,N/A,N/A,NO
71,2016/12/01 00:00:00,Dec 1; 2036 23:59:56,N/A,N/A,NO
72,2016/12/01 00:00:00,Dec 1; 2036 23:59:56,N/A,N/A,NO
73,2016/12/01 00:00:00,Dec 1; 2036 23:59:56,N/A,N/A,NO
74,2016/12/01 00:00:00,Dec 1; 2036 23:59:56,N/A,N/A,NO
75,2016/12/01 00:00:00,Dec 1; 2036 23:59:56,N/A,N/A,NO