how to convert String to date in talend

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

66 REPLIES
Community Manager

Re: how to convert String to date in talend

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
----------------------------------------------------------
Talend | Data Agility for Modern Business
Moderator

Re: how to convert String to date in talend

Hi,
And use TalendDate.parseDate(String, String) method in tMap or tConvertType component.
See my screenshots
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.

Re: how to convert String to date in talend

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
Moderator

Re: how to convert String to date in talend

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
--
Don't forget to give kudos when a reply is helpful and click Accept the solution when you think you're good with it.
One Star

Re: how to convert String to date in talend

23.09.2011 looks like dd.MM.yyyy format Smiley Happy
Moderator

Re: how to convert String to date in talend

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
--
Don't forget to give kudos when a reply is helpful and click Accept the solution when you think you're good with it.

Re: how to convert String to date in talend

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
Moderator

Re: how to convert String to date in talend

Hi,
Show me your tMap editor screenshot so that I can get your current situation more precisely, please.
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.

Re: how to convert String to date in talend

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

Re: how to convert String to date in talend

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
--
Don't forget to give kudos when a reply is helpful and click Accept the solution when you think you're good with it.
One Star

Re: how to convert String to date in talend

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
One Star

Re: how to convert String to date in talend

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
One Star

Re: how to convert String to date in talend

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
One Star

Re: how to convert String to date in talend

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

Re: how to convert String to date in talend

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
One Star

Re: how to convert String to date in talend

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!
One Star

Re: how to convert String to date in talend

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?
One Star

Re: how to convert String to date in talend

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?
One Star

Re: how to convert String to date in talend

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?.............
One Star

Re: how to convert String to date in talend

yes, I was about to ask you this only. The nullpointer exception is comming because you have nulls in your source. It cannot convert null to date. That is the error here.
One Star

Re: how to convert String to date in talend

So what you can do is handle the nulls in an "if" condition. I think you would be better off using a java component here. You can give a tjavarow component after your data fetch and use the following code :
output_row.ID = input_row.ID;
output_row.Name = input_row.Name;
output_row.DOB = TalendDate.parseDate("dd/mm/yyyy",input_row.DOB);
output_row.Sal = input_row.Sal;
if (input_row.HD != null)
output_row.HD = TalendDate.parseDate("dd/mm/yyyy",input_row.HD);
else
input_row.HD = null;

the nulls would be handled in the if condition.
One Star

Re: how to convert String to date in talend

I use the following syntax when converting dates formats from strings

StringUtils.isBlank(row20.Execution_Date) ? " " : TalendDate.formatDate("yyyy-MM-dd",TalendDate.parseDate("MM/dd/yyyy",row20.Execution_Date))
One Star

Re: how to convert String to date in talend

HI remytom,
Thank you for your reply , now i have a good result ..................
can you guide me how to install talend enterprise data integration tool on windows 7, if you can because i have more link while iam downloading it. like installer and manual installing
One Star

Re: how to convert String to date in talend

I could guide you better if you mention what help you need in the installation specifically.
Which version of talend are you installing?
One Star

Re: how to convert String to date in talend

Hi remytom,
Iam going to install talend enterprise date integration version 5.4.1 so for that i have two mode to install it .
one is manual installer and second is installer.
In installer i have only two links but in manual installation i have the following links
1.Talend studio,2.Talend administration center,3.Talend job server,4.Talend AMC Web,5.Talend BRMS.

What i have to do now.
One Star

Re: how to convert String to date in talend

Hi anyone can help me how to solve this problem...........

I have a date in my Excel file is 01-JAN-06. 01 represents the day of the week, JAN represents the month and 06 represents the year. The Excel file also contains date values of 00-XXX-00 which represent no date. For example a column containing last purchase date data would look like this:"DateOfLastOrder"
"01-JAN-06"
"02-JAN-06"
"00-XXX-00"
"03-DEC-05"The value of 00-XXX-00 means that there is no purchase date.I want to bring these columns into my table and replace the 00-XXX-00 values with a NULL.
The table of tmssqloutput Data Type is datetime.
One Star

Re: how to convert String to date in talend

Hi anyone can help me how to solve this problem...........

I have a date in my Excel file is 01-JAN-06. 01 represents the day of the week, JAN represents the month and 06 represents the year. The Excel file also contains date values of 00-XXX-00 which represent no date. For example a column containing last purchase date data would look like this:"DateOfLastOrder"
"01-JAN-06"
"02-JAN-06"
"00-XXX-00"
"03-DEC-05"The value of 00-XXX-00 means that there is no purchase date.I want to bring these columns into my table and replace the 00-XXX-00 values with a NULL.
The table of tmssqloutput Data Type is datetime.
This is the error iam getting Smiley Surprisednly the date between january 1 1753 and december 31 accepted.
One Star

Re: how to convert String to date in talend

Hi Mahadevank,
Sorry I could not help you with the installation!
But regarding your latest post, Could you tell in what date format you are reading the dates in talend and what is the date formats in the target? Have you checked the date formats in your target component (tmssqloutput in talend) and the SQL server database?
Regarding the replacement of the 00-XXX-00 values, what ae you using? Allthough the error does not seem because of it. But still, If you incorporate this code in a tjava component after reading the input file, it should handle all your non- date entries.
if (TalendDate.isDate(input_row.HD,"dd-MM-yyyy") == true)
output_row.HD = TalendDate.parseDate("dd-MM-yyyy",input_row.HD);
else
output_row.HD = null;
One Star

Re: how to convert String to date in talend

Hi ,
i have started to intall talend enterprise edition 5.4.1 on windows 7 32 bit , its taking more than 24 hours still its not completed . can any one provide me what is the misstake i did...........
i have chosen talend installer option not manual installation.
I have selected all the option which is available in talend enterprise edition like administration center,job server, svn repository, every thing ..........