[resolved] Convert date format

[Anonymous]

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.

[Anonymous]

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]

[Anonymous]

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.

[Anonymous]

I manually edited the circled part in the ink.

 


Shows an error I do not understand.

[Anonymous]

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? 

[Anonymous]

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

[Anonymous · Accepted solution]

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

[Anonymous]

Thanks Shong!!!

[Anonymous]

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

[Anonymous]

Its really urgent, Please help me with the same.