topic Re: how to convert String to date in talend in Talend Studio

how to convert String to date in talend

Anonymous — Wed, 18 Sep 2013 08:12:45 GMT

hi,
i have date which is in string "MM/dd/yyyy" and i want to convert same format in DATE data type in talend,
means
"MM/dd/yyyy" data type(String) ---> MM/dd/yyyy data type(DATE)
how could i do this please provide the solution.
thanks and regards
Shailendra Shukla

Re: how to convert String to date in talend

Anonymous — Wed, 18 Sep 2013 08:26:25 GMT

Hi
You can use routine function TalendDate.parseDate("MM/dd/yyyy", yourStringData) to convert a string to a Date, for example:
tFileInputDelimited--main--tJavaRow--tlogRow
on tJavaRow:
outout_row.columnName=TalendDate.parseDate("MM/dd/yyyy", input_row.columnName);
If you want to learn more about the built in function TalendDate.parseDate("MM/dd/yyyy", yourStringData), expand Code node in Repository, then Routine, System.
Shong

Re: how to convert String to date in talend

Anonymous — Wed, 18 Sep 2013 08:28:01 GMT

Hi,
And use TalendDate.parseDate(String, String) method in tMap or tConvertType component.
See my screenshots
Best regards
Sabrina

Re: how to convert String to date in talend

Anonymous — Wed, 18 Sep 2013 08:56:52 GMT

hi Xdshi,
thanks for your response but when i am using this format as u gave in the snap shot .
it throws an error like..
Exception in component tMap_1
java.lang.RuntimeException: java.text.ParseException: Unparseable date: "23.09.2011"
at routines.TalendDate.parseDate(TalendDate.java:643)
at ok.date_formate_0_1.DATE_FORMATE.tFileList_1Process(DATE_FORMATE.java:7544)
at ok.date_formate_0_1.DATE_FORMATE.runJobInTOS(DATE_FORMATE.java:10340)
at ok.date_formate_0_1.DATE_FORMATE.main(DATE_FORMATE.java:10194)
Caused by: java.text.ParseException: Unparseable date: "23.09.2011"
at java.text.DateFormat.parse(Unknown Source)
at routines.TalendDate.parseDate(TalendDate.java:641)
... 3 more
i used this format --TalendDate.parseDate("MM/dd/yyyy",row1.Clearing_Date)
thanks and regards
Shailendra shukla

Re: how to convert String to date in talend

Anonymous — Wed, 18 Sep 2013 09:56:06 GMT

Hi,
If your input string is 23.09.2011, you should change the format as TalendDate.parseDate("MM.dd.yyyy",row1.Clearing_Date).
Best regards
Sabrina

Re: how to convert String to date in talend

Anonymous — Wed, 18 Sep 2013 10:28:06 GMT

23.09.2011 looks like dd.MM.yyyy format

Re: how to convert String to date in talend

Anonymous — Wed, 18 Sep 2013 10:39:29 GMT

Hi,
Sorry for the error. It is TalendDate.parseDate("dd.MM.yyyy",row1.Clearing_Date) for data 23.09.2011.
Thanks for correction.
@shailendra.shukla, is your input MM/dd/yyyy string?
Best regards
Sabrina

Re: how to convert String to date in talend

Anonymous — Wed, 18 Sep 2013 11:07:50 GMT

Thanks Xdshi ,
yup my input date in string, now its working.
but i want the output in MM/dd/YYYY but it gives as input format
and output is look like -
09/30/2011--->30-SEP-11
but i want in MM/dd/yyyy
i used this format
(Var.date==null || Var.date.isEmpty())? null : TalendDate.parseDate("MM/dd/yyyy",TalendDate.formatDate("MM/dd/yyyy",TalendDate.parseDate("MM/dd/yyyy",Var.date)))
regards
shailendra shukla

Re: how to convert String to date in talend

Anonymous — Wed, 18 Sep 2013 11:15:32 GMT

Hi,
Show me your tMap editor screenshot so that I can get your current situation more precisely, please.
Best regards
Sabrina

Re: how to convert String to date in talend

Anonymous — Wed, 18 Sep 2013 11:30:18 GMT

sorry but i m unable to take screenshot, but i used below format
(Var.date==null || Var.date.isEmpty())? null : TalendDate.parseDate("MM/dd/yyyy",TalendDate.formatDate("MM/dd/yyyy",TalendDate.parseDate("MM/dd/yyyy",Var.date)))
and gave date pattern like MM/dd/yyyy.
my input date is MM/dd/yyyy.
after executing the job the output is look like. 09/30/2011(input)--->30-SEP-11(output)

Re: how to convert String to date in talend

Anonymous — Mon, 23 Sep 2013 05:12:13 GMT

Hi,

(Var.date==null || Var.date.isEmpty())? null : TalendDate.parseDate("MM/dd/yyyy",TalendDate.formatDate("MM/dd/yyyy",TalendDate.parseDate("MM/dd/yyyy",Var.date)))

You use a Var in your tMap? How to define it in your tMap?
Best regards
Sabrina

Re: how to convert String to date in talend

Anonymous — Fri, 24 Jan 2014 12:39:03 GMT

Hi , i have a source in excel with the following data
id name dateofbirth salary hiredate
1 xays 24/01/1994 100 05/07/2012
2 xyz 22/10/1991 200 04/02/2012
3 kjsdj 04/02/1990 300 14/01/2008
4 dfdkl 06/06/1989 400 16/02/2010
here rowno1 24/01/1994,dateofbirth is displaying in excel as a general format
and rowno2 22/10/1994 dateofbirth is displaying in excel as a general format
and rowno 4 16/02/2010 hiredate is displaying in excel as a general format. so while iam loading this from excel to target iam getting the following error
The cell format is not date in row1 date of birth
The cell format is not date in row2 date of birth
The cell format is not date in row4 hire date Because the above mentioned rows are in general format in excel sheet.
my expetation is i want to get all the rows from source to target

Re: how to convert String to date in talend

Anonymous — Fri, 24 Jan 2014 13:40:02 GMT

Hi Mahadevank,
You can mention the data type of your dates as string while defining schema to fetch data from the excel file. Then using Talenddate.parsedate("mm/dd/yyyy", datestring) in tjavarow or any other component which supports expression, you can change the datatype of your fetched dates.
Hope this helps

Re: how to convert String to date in talend

Anonymous — Mon, 27 Jan 2014 11:07:05 GMT

Hi, remytom
thank you for your fast response..........
Here i used parsedate expression TalendDate.parseDate("dd/MM/yyyy".row1.date) in tmap while fetching the data from excel and the date columns are as a string datatype as you said in your reply.

But if i used parsedate in tmap the complete date value has been changed like.....
row1 date is 24/01/1994 as changed to like this 01/12/95 and
row2. 22/10/1991 is changed to 10/10/19992
can you show me the screen shot please for this solution

Re: how to convert String to date in talend

Anonymous — Mon, 27 Jan 2014 15:11:52 GMT

I am not getting your error. If you could give me the snapshot of your tmap, I could understand better. Attaching herewith the snapshot of my solution. (I have converted only one of the date columns here and it works)

Re: how to convert String to date in talend

Anonymous — Wed, 29 Jan 2014 05:41:48 GMT

Hi remytom,

Yes i did in the way you said and here iam getting half rows only ...........
Because in my job i wrote parse date funtion in tmap for dateofbirth, hiredate column so i got half result what about another column (Hiredate).
If i write the parse date function also for leavedate column means its providing error. see my screen shot for your review

Re: how to convert String to date in talend

Anonymous — Wed, 29 Jan 2014 08:18:49 GMT

when you apply the conversion on both the columns, you are getting incorrect dates? Or some other error is coming up?
A screenshot of your tmap would be great!

Re: how to convert String to date in talend

Anonymous — Wed, 29 Jan 2014 10:38:08 GMT

Hi remyton ,
when iam applying the conversion on single column, i got some rows of data.
But when Iam applying the conversion on both column iam getting some different error here..............
And i have some null row in leave date, is this a problem?

Re: how to convert String to date in talend

Anonymous — Wed, 29 Jan 2014 12:42:54 GMT

I am sorry .Your snapshot did not get uploaded. I am not able to understand the error you are getting. Could you paste the error message?

Re: how to convert String to date in talend

Anonymous — Wed, 29 Jan 2014 14:11:50 GMT

Hi remytom
see my previous post there i uploaded my snopshot . That is the error i got .
i have applied parse date function for two column 1.dateofbirth,2.hiredate. when iam applying the parse date function for one more column leave date , i got the error.

In leave date column i have some null values, is this a problem?.............