topic Re: String to date conversion in Talend Studio

String to date conversion

Anonymous — Wed, 09 May 2018 05:29:15 GMT

My input from database is having "date" in string datatype with size 50, which is sending date in yyyy-mm-dd format. I want to store it at the destination in String(50) with mm-dd-yyyy format. Solution please.

Re: String to date conversion

KarthikGs — Wed, 09 May 2018 05:54:32 GMT

hi,

to convert string to date you have to use talendDate routines available with in the talend code repo.

Here is how you can convert a string in to date.

TalendDate.parseDate("format date of the string to be parsed", "string in the format of the date to be parsed")

And to limit the length of the string, define its size in the schema.

Thanks

Re: String to date conversion

TRF — Wed, 09 May 2018 06:00:45 GMT

Try this:
TalendDate.formatDate("mm-dd-yyyy", TalendDate.parseDate("yyyy-mm-dd", row1.yourDatabaseField))

Re: String to date conversion

Anonymous — Wed, 09 May 2018 06:15:41 GMT

I am getting error. Actually, the input field which is sending date is string(50) and output field is also in string(50). Please suggest.

Or, Can we just shuffle it using only string function? like, currently i am getting yyyy-mm-dd, and shuffle it to mm-dd-yyyy. is it possible with any function?

Re: String to date conversion

Anonymous — Wed, 09 May 2018 06:56:11 GMT

Hello,

What's the error are you getting?

Could you please post your current job setting screenshot on forum? Are you using tMap component in your job?

Best regards

Sabrina

Re: String to date conversion

Anonymous — Wed, 09 May 2018 07:01:37 GMT

i have attached the screen shot, please suggest

error is:

[statistics] connecting to socket on port 3456
[statistics] connected
java.lang.NumberFormatException: For input string: "row2"
at java.lang.NumberFormatException.forInputString(Unknown Source)
at java.lang.Integer.parseInt(Unknown Source)
at java.lang.Integer.parseInt(Unknown Source)
at routines.system.FastDateParser$DateParser.parse(FastDateParser.java:127)
at java.text.DateFormat.parse(Unknown Source)
at routines.TalendDate.parseDate(TalendDate.java:884)
at routines.TalendDate.parseDate(TalendDate.java:842)
at ocm.ocm_data_extraction_0_1.OCM_Data_Extraction.tDBInput_5Process(OCM_Data_Extraction.java:2846)
at ocm.ocm_data_extraction_0_1.OCM_Data_Extraction.tDBInput_2Process(OCM_Data_Extraction.java:1014)
at ocm.ocm_data_extraction_0_1.OCM_Data_Extraction.runJobInTOS(OCM_Data_Extraction.java:3648)
at ocm.ocm_data_extraction_0_1.OCM_Data_Extraction.main(OCM_Data_Extraction.java:3497)
Exception in component tMap_2 (OCM_Data_Extraction)
java.lang.RuntimeException: java.text.ParseException: Unparseable date: "row2.DiagnosisActivityStartDate"
at routines.TalendDate.parseDate(TalendDate.java:898)
at routines.TalendDate.parseDate(TalendDate.java:842)
at ocm.ocm_data_extraction_0_1.OCM_Data_Extraction.tDBInput_5Process(OCM_Data_Extraction.java:2846)
at ocm.ocm_data_extraction_0_1.OCM_Data_Extraction.tDBInput_2Process(OCM_Data_Extraction.java:1014)
at ocm.ocm_data_extraction_0_1.OCM_Data_Extraction.runJobInTOS(OCM_Data_Extraction.java:3648)
at ocm.ocm_data_extraction_0_1.OCM_Data_Extraction.main(OCM_Data_Extraction.java:3497)
Caused by: java.text.ParseException: Unparseable date: "row2.DiagnosisActivityStartDate"
at java.text.DateFormat.parse(Unknown Source)
at routines.TalendDate.parseDate(TalendDate.java:884)
... 5 more
[statistics] disconnected

Graph1.jpg

Re: String to date conversion

TRF — Wed, 09 May 2018 07:14:09 GMT

You need to remove double quotes arround row2.DiagnosisActivityStartDate in tne Modified_Data flow

Re: String to date conversion

Anonymous — Wed, 09 May 2018 07:24:45 GMT

Thanks TRF.... its resolved....

Re: String to date conversion

Anonymous — Wed, 09 May 2018 08:15:08 GMT

earlier it ran fine. Now its showing below error.

I just noticed that, some of the fields from the input is having blank in date field. How to handle this

statistics] connecting to socket on port 4016
[statistics] connected
java.lang.StringIndexOutOfBoundsException: String index out of range: 4
at java.lang.String.substring(Unknown Source)
at routines.system.FastDateParser$DateParser.parse(FastDateParser.java:127)
at java.text.DateFormat.parse(Unknown Source)
at routines.TalendDate.parseDate(TalendDate.java:884)
at routines.TalendDate.parseDate(TalendDate.java:842)
at ocm.ocm_data_extraction_0_1.OCM_Data_Extraction.tDBInput_5Process(OCM_Data_Extraction.java:2762)
at ocm.ocm_data_extraction_0_1.OCM_Data_Extraction.tDBInput_2Process(OCM_Data_Extraction.java:998)
at ocm.ocm_data_extraction_0_1.OCM_Data_Extraction.runJobInTOS(OCM_Data_Extraction.java:3589)
at ocm.ocm_data_extraction_0_1.OCM_Data_Extraction.main(OCM_Data_Extraction.java:3438)
Exception in component tMap_2 (OCM_Data_Extraction)
java.lang.RuntimeException: java.text.ParseException: Unparseable date: ""
at routines.TalendDate.parseDate(TalendDate.java:898)
at routines.TalendDate.parseDate(TalendDate.java:842)
at ocm.ocm_data_extraction_0_1.OCM_Data_Extraction.tDBInput_5Process(OCM_Data_Extraction.java:2762)
at ocm.ocm_data_extraction_0_1.OCM_Data_Extraction.tDBInput_2Process(OCM_Data_Extraction.java:998)
at ocm.ocm_data_extraction_0_1.OCM_Data_Extraction.runJobInTOS(OCM_Data_Extraction.java:3589)
at ocm.ocm_data_extraction_0_1.OCM_Data_Extraction.main(OCM_Data_Extraction.java:3438)
Caused by: java.text.ParseException: Unparseable date: ""
at java.text.DateFormat.parse(Unknown Source)
at routines.TalendDate.parseDate(TalendDate.java:884)
... 5 more
[statistics] disconnected

Re: String to date conversion

TRF — Wed, 09 May 2018 08:30:10 GMT

Should be a new case...

Anyway, you may exclude records when the data content lenght is less than 10 characters.

Use a tFilterRow for that.

Re: String to date conversion

Anonymous — Wed, 09 May 2018 09:16:15 GMT

i shoudn't drop records, because after this transmission those blank date fields will be having a data thru front end. I need to pass these blanks. Hence i did like below in tMap but it didnt work. Please suggest.

row2.DiagnosisActivityStartDate == "" ? "" : TalendDate.formatDate("MM-dd-yyyy",TalendDate.parseDate("yyyy-MM-dd",row2.DiagnosisActivityStartDate))

Re: String to date conversion

TRF — Wed, 09 May 2018 10:10:47 GMT

"".equals(row2.DiagnosisActivityStartDate) || 
row2.DiagnosisActivityStartDate == null ? "" : TalendDate.formatDate("MM-dd-yyyy",TalendDate.parseDate("yyyy-MM-dd",row2.DiagnosisActivityStartDate))

Re: String to date conversion

Anonymous — Wed, 09 May 2018 10:21:05 GMT

Wow TRF... it worked.. Thanks again

Re: String to date conversion

Anonymous — Mon, 14 May 2018 07:32:26 GMT

Hello TRF,

Can you please explain the statement you have given for the solution?

"".equals(row2.DiagnosisActivityStartDate) || 
row2.DiagnosisActivityStartDate == null ? "" : TalendDate.formatDate("MM-dd-yyyy",TalendDate.parseDate("yyyy-MM-dd",row2.DiagnosisActivityStartDate))

It worked actually, but the output excel's cell is in "General" format and i am expecting in "Custom" format cell with MM-DD-YYYY option (just like the attached image) then only the formula will pick this cell for calculation. Please guide

Graph9.jpg