topic Re: [resolved] Convert date format in Talend Studio

[resolved] Convert date format

Anonymous — Thu, 22 Dec 2016 10:13:06 GMT

Hi There,

I have a table which contains the date in varchar form : "2016-09-06T10:05:59.226000Z". I want to convert the same in "2016-09-06 10:05:59". Please help as I am new to this platform.

My Data is in MYSQL.

Re: [resolved] Convert date format

Anonymous — Sun, 25 Dec 2016 12:09:53 GMT

Hi
What's the data type of target field? Assuming it is a Date field, read the input data as a string, and then convert it to Date with this expression on tMap:
tMysqlInput--main(row1)--tMap-->...
[font=Verdana, Helvetica, Arial, sans-serif] TalendDate.parseDate("yyyy-MM-dd'T'HH:mm:ss.SSSSSS'Z'",row1.colName)[/font]

[font=Verdana, Helvetica, Arial, sans-serif] Regards[/font]
[font=Verdana, Helvetica, Arial, sans-serif] Shong[/font]

Re: [resolved] Convert date format

Anonymous — Tue, 27 Dec 2016 17:16:48 GMT

My data source is Google sheets which I am transforming into a CSV file. The Column is "Week" and it is in the format MM/dd/yyyy. When I designate "Week" as Date with format MM/dd/yyyy in the "Edit Schema" and load into postgreSQL (I have used both tMap as intermediary and directly connected tFileInputDelimited to tPostgresqlOutput) it shows up as timestamp without time zone in the postgreSQL database. I even tried the method you have suggested above. Designated "Week" as String in "Edit Schema" and in tMap formatted the column as

TalendDate.parseDate("MM/dd/yyyy",row1.Week)

The database still shows as Timestamp without time zone

The result I am getting in database:
2013-01-06 00:00:00

The result I am requesting help:
01/06/2013.

Re: [resolved] Convert date format

Anonymous — Tue, 27 Dec 2016 23:11:43 GMT

I manually edited the circled part in the ink.

Shows an error I do not understand.

Re: [resolved] Convert date format

Anonymous — Wed, 28 Dec 2016 07:37:36 GMT

It fails to parse the string to Date with the pattern, what's the data looks? Can you please show me the full error message?

Re: [resolved] Convert date format

Anonymous — Wed, 28 Dec 2016 14:13:01 GMT

Here is the error:
Exception in component tMap_1
java.lang.RuntimeException: java.text.ParseException: Unparseable date: ""
at routines.TalendDate.parseDate(TalendDate.java:895)
at routines.TalendDate.parseDate(TalendDate.java:839)
at rehearsalproject.salesdata_0_1.SalesData.tFileInputDelimited_1Process(SalesData.java:2609)
at rehearsalproject.salesdata_0_1.SalesData.tFileFetch_1Process(SalesData.java:483)
at rehearsalproject.salesdata_0_1.SalesData.runJobInTOS(SalesData.java:3431)
at rehearsalproject.salesdata_0_1.SalesData.main(SalesData.java:3288)
Caused by: java.text.ParseException: Unparseable date: ""
at java.text.DateFormat.parse(Unknown Source)
at routines.TalendDate.parseDate(TalendDate.java:881)
... 5 more

Re: [resolved] Convert date format

Anonymous — Thu, 29 Dec 2016 06:59:45 GMT

Hello

Exception in component tMap_1
java.lang.RuntimeException: java.text.ParseException: Unparseable date: ""

There exists empty value in this column, you need to set it as original value or a default Date if it is empty. eg:
row1.Week.equals("")?"":TalendDate.parseDate("MM/dd/yyyy",row1.Week)

Regards
Shong

Re: [resolved] Convert date format

Anonymous — Thu, 29 Dec 2016 16:19:27 GMT

Thanks Shong!!!

Re: [resolved] Convert date format

Anonymous — Mon, 02 Mar 2020 13:40:37 GMT

Hi Team,

I have a date column which has differnet type of date in same column from my CSv file. Please find the below sample for the same -

30-09-2019 05:51:53

Sat, 28 Sep 2019 12:15:11

24-09-2019 14:54:16

Sun, 22 Sep 2019 14:09:39

Fri, 20 Sep 2019 12:20:19

Mon, 23 Sep 2019 20:29:49

23-09-2019 16:58:08

30-09-2019 09:21:58

Error :

Exception in component tMap_1 (App_Visit_Data)
java.lang.RuntimeException: java.text.ParseException: Unparseable date: "Wed, 2 Oct 2019 05:21:36"
at routines.TalendDate.parseDate(TalendDate.java:895)
at routines.TalendDate.parseDate(TalendDate.java:839)
at alghanim_project.app_visit_data_0_1.App_Visit_Data.tFileInputDelimited_1Process(App_Visit_Data.java:1565)
at alghanim_project.app_visit_data_0_1.App_Visit_Data.runJobInTOS(App_Visit_Data.java:2255)
at alghanim_project.app_visit_data_0_1.App_Visit_Data.main(App_Visit_Data.java:2104)
Caused by: java.text.ParseException: Unparseable date: "Wed, 2 Oct 2019 05:21:36"
at java.text.DateFormat.parse(Unknown Source)
at routines.TalendDate.parseDate(TalendDate.java:881)
... 4 more
[statistics] disconnected
Job App_Visit_Data ended at 19:03 02/03/2020. [exit code=1]

Please help

Screen1 error.JPG
Screen2.JPG

Re: [resolved] Convert date format

Anonymous — Mon, 02 Mar 2020 13:43:28 GMT

Its really urgent, Please help me with the same.