Four Stars

How to convert a special date

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

  • Data Integration
1 ACCEPTED SOLUTION

Accepted Solutions
Four Stars

Re: How to convert a special date

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 .

 

tmap.JPG

 

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

11 REPLIES
Four Stars

Re: How to convert a special date

Sorry ,

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

 

Dec 1; 2016 00:00:00

Ten Stars

Re: How to convert a special date

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?

Four Stars

Re: How to convert a special date

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

 

 

 

Ten Stars

Re: How to convert a special date

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?
Four Stars

Re: How to convert a special date

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 ?

 

Ten Stars

Re: How to convert a special date

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.

Four Stars

Re: How to convert a special date

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]

Ten Stars

Re: How to convert a special date

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.

Talend expression builder.png

[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]
Four Stars

Re: How to convert a special date

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

 

tmap.JPG

 

 

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


Ten Stars

Re: How to convert a special date

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.

Four Stars

Re: How to convert a special date

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 .

 

tmap.JPG

 

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