topic Re: How to convert string to date format! in Talend Studio

How to convert string to date format!

Anonymous — Sat, 16 Nov 2024 12:38:10 GMT

Hi Talend Support officer,
Can you tell me how can I convert the string data of "14/09/2001" from MS Access to DB2 date format ("yyyy-mm-dd") as "2001-09-14" by using tMap function in Talend Open Studio, please?
Thanks.
Regards,
KM

Re: How to convert string to date format!

djugal — Mon, 10 Oct 2011 07:18:01 GMT

hi,
first convert the column to built-in schema & edit your date column from string to date datatype format "dd-mm-yyyy"
then in tmap use function TalendDate.formatDate("yyyy-MM-dd",myDate) to get your desired format.
regards,
Jugal

Re: How to convert string to date format!

Anonymous — Mon, 10 Oct 2011 08:42:32 GMT

Hi Jugal,
Thanks for the tips. I still having problem after I changed the column of the datatype from string to date for the input column. Then in tmap, I put the function as TalendDate.parseDate("yyyy-MM-dd",PATIENT.Patient_DOB). It still giving me this error as => Exception in thread "main" java.lang.Error: Unresolved compilation problem:
The method parseDate(String, String) in the type TalendDate is not applicable for the arguments (String, Date).
Unfortunately, I am unable to provide you the dump screen due to network bandwidth limitation. Can you help me with this error, please? Thanks.
Regards,
KM

Re: How to convert string to date format!

djugal — Mon, 10 Oct 2011 10:50:39 GMT

Try this function TalendDate.formatDate("yyyy-MM-dd",myDate)

Re: How to convert string to date format!

Anonymous — Mon, 10 Oct 2011 13:13:37 GMT

Hi
Use this way
TalendDate.parseDate("yyyy-MM-dd",TalendDate.formatDate("dd/MM/yyyy"))
This case work if the incoming data type is string else use parseDate directly with pattern "dd/MM/yyyy"

Re: How to convert string to date format!

_AnonymousUser — Thu, 16 May 2013 06:50:01 GMT

The problem is i have a string "2008013008013661" i got to truncate it to "20080130080136" then convert this string to date format which i should be able to insert into mySql db

Re: How to convert string to date format!

Anonymous — Wed, 18 Sep 2013 11:29:49 GMT

I have a date object, and for NULL date i want to store blank in excel file. Please suggest any way

Re: How to convert string to date format!

Anonymous — Wed, 18 Sep 2013 14:19:59 GMT

use Relational.ISNULL function to check if null. If null pass as null object

Re: How to convert string to date format!

Anonymous — Tue, 28 Jan 2014 05:59:45 GMT

Hi can any one help me to solve this problem..........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.
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 format!

Anonymous — Tue, 28 Jan 2014 09:56:19 GMT

Hi Mahadevank,
Have you checked the date format of your output schema in tmap?

Re: How to convert string to date format!

Anonymous — Thu, 30 Jan 2014 10:27:23 GMT

Hi remytom,
Thank you for your reply ,i got a proper result.

Re: How to convert string to date format!

Anonymous — Tue, 04 Feb 2014 10:47:22 GMT

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.

Re: How to convert string to date format!

Anonymous — Tue, 04 Feb 2014 10:48:29 GMT

Re: How to convert string to date format!

Anonymous — Wed, 05 Feb 2014 09:24:31 GMT

This code could help for the replacement. This is used in tjavarow component after the file is read.
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;

Re: How to convert string to date format!

Anonymous — Thu, 13 Feb 2014 05:45:03 GMT

Hi, any one can provide me one snapshot for joblet with example, because i am confusing with schema while creating joblet in talend enterprise edition.
here i am doing concatinating fname+lastname=full name in variable tmap
Here i have used schema for emp table for joblet and how can i use this for another job .............
In another job i have customer table.............

Re: How to convert string to date format!

Anonymous — Thu, 13 Feb 2014 06:30:04 GMT

Hi mahadevank,
Have you ever checked the document TalendHelpCenteresigning+a+Joblet.
Best regards
Sabrina

Re: How to convert string to date format!

Anonymous — Thu, 13 Feb 2014 09:45:26 GMT

Hi, Yes already i have studied talend help centre about joblet but i can't understand...........
In my job i have a source like this....... emp table
empid firstname lastname gender maritalstatus dob
here i created joblet with tmap,sorter,filter
joblet.................
INPUT(emptable)-tmap-sorter-filter-OUTPUT.
joblet mean> reuse this transformation logic for multiple job.
But here i used emp table from INPUT to OUTPUT with all the above mentioned component in joblet.
so if i want to use this joblet for another job means i have to use this only for emp table as a source not for another table as a source in this job.
My question is:
1. So what is the use for joblet........... when i have a customer table with the following columns
customerid firstname lastname city phoneno email in a job (here how to use that joblet)
2. And In talend help center they created one joblet for one source and they used this joblet in another job without mentioning the source of a job. whether it is same source or different.
If it is same what is the use of it..........
3. I need one example joblet and one job using (that joblet) to understand with schema for both.

Re: How to convert string to date format!

Anonymous — Mon, 03 Mar 2014 13:43:28 GMT

Hi any one can help me to solve this issue
I have a source in excel like id name date
10 Ravi 18/05/2014
20 Naraine 06/12/2013
30 Mahesh 23/10/2012
40 Ganesh 09/10/2010
Note :
Now i want to load the entire rows of data in to tmssqloutput, but iam getting only the rows which in MM/dd/ yyyy format in source.
Because its considering the source in MM/dd/yyyy format , so i got only the rows which is less than 12 month like 09/10/2010 and 06/12/2013.

But i entered the rows in excel in the meaning of dd/MM/yyyy............... if i used parsed date fuction in tmap i got all the rows. this is only ok for tlogrow but not for tmssqloutput.
if i used tmssqloutput i got this error...... only the date between january 1 1753 and december 31 9999 are accepted. Any solution for this..............

Re: How to convert string to date format!

Anonymous — Tue, 04 Mar 2014 06:47:34 GMT

Hi mahadevank,

But i entered the rows in excel in the meaning of dd/MM/yyyy............... if i used parsed date fuction in tmap i got all the rows. this is only ok for tlogrow but not for tmssqloutput.

How did you parsed date fuction in tmap? What the error looks like? Data truncation?
Best regards
Sabrina

Re: How to convert string to date format!

Anonymous — Tue, 04 Mar 2014 09:22:43 GMT

Hi thanks for your response..................Here i explainde my job and error, which i got.
JOB: tFileInputExcel- tmap- tmssqloutput
1. if i used Date column with date datatype in tfileinputexcel, i got only few rows like which i said on previous post and my error is "The cell format is not Date in row 1 and column 4
"The cell format is not Date in row 3 and column 4.
2. If i used Date column with string datatype in tfileinputexcel , i did not get any row in tmssqloutput
Tfileinputecxel(Date date)-tmap output as date datatype. error is Only dates between January 1, 1753 and December 31, 9999 are accepted.
My expectation is , i want to load all the rows of data into tmssqloutput.........