Five Stars

String to date conversion

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.

1 ACCEPTED SOLUTION

Accepted Solutions
Thirteen Stars TRF
Thirteen Stars

Re: String to date conversion

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

TRF
13 REPLIES
Seven Stars

Re: String to date conversion

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

Thirteen Stars TRF
Thirteen Stars

Re: String to date conversion

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

TRF
Five Stars

Re: String to date conversion

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? 

Moderator

Re: String to date conversion

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

--
Don't forget to give kudos when a reply is helpful and click Accept the solution when you think you're good with it.
Five Stars

Re: String to date conversion

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

Thirteen Stars TRF
Thirteen Stars

Re: String to date conversion

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

TRF
Five Stars

Re: String to date conversion

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

Five Stars

Re: String to date conversion

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

Thirteen Stars TRF
Thirteen Stars

Re: String to date conversion

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.


TRF
Five Stars

Re: String to date conversion

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)) 

Thirteen Stars TRF
Thirteen Stars

Re: String to date conversion

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

TRF
Five Stars

Re: String to date conversion

Wow TRF... it worked.. Thanks again

Five Stars

Re: String to date conversion

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