One Star

How to convert string to date format!

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

58 REPLIES
Four Stars

Re: How to convert string to date format!

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

Re: How to convert string to date format!

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
Four Stars

Re: How to convert string to date format!

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

Re: How to convert string to date format!

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

Re: How to convert string to date format!

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

Re: How to convert string to date format!

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

Re: How to convert string to date format!

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

Re: How to convert string to date format!

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

Re: How to convert string to date format!

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

Re: How to convert string to date format!

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

Re: How to convert string to date format!

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 format!

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: only the date between january 1 1753 and december 31 9999 are accepted .
One Star

Re: How to convert string to date format!

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

Re: How to convert string to date format!

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

Re: How to convert string to date format!

Hi mahadevank,
Have you ever checked the document TalendHelpCenterSmiley Very Happyesigning+a+Joblet.
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 format!

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

Re: How to convert string to date format!

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

Re: How to convert string to date format!

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
--
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 format!

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

Re: How to convert string to date format!

Hi
Here is an issue on http://stackoverflow.com/questions/468045/error-sqldatetime-overflow-must-be-between-1-1-1753-120000.... We will make an investigation on it then come back to you as soon as we can.
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.
Moderator

Re: How to convert string to date format!

Hi mahadevank,
So far, the range of sql server datetime is "1753-01-01 through 9999-12-31".
Please take a look at library http://technet.microsoft.com/en-us/library/ms186724.aspx.
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 format!

Hi, Thank you for your quick response,
Sorry i can't understand what you are saying from this link..................
my expectation is i have a source in excel in dd/MM/yyyy format, i just want to convert it to yyyy/MM/dd format to load the data to tmssqloutput..........
Moderator

Re: How to convert string to date format!

Hi,
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.

The range of sql server datetime is "1753-01-01 through 9999-12-31", that's the reason why you got a error "dates between January 1, 1753 and December 31, 9999 are accepted".
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 format!

Can you check this?
TalendDate.parseDate("dd/MM/yyyy",row1.ExcelDateCol)
If your source col has null values, then
(row1.ExcelDateCol!=null)?TalendDate.parseDate("dd/MM/yyyy",row1.ExcelDateCol):null
Even if you change in tMap, check out the DB datatype column in the target column... If its dd/MM/yyyy, then even if you convert it to any format, it takes only the target format defined in the DB Datatype
So change the Date Pattern to "yyyy/MM/dd" or as desired...
One Star

Re: How to convert string to date format!

Hi xdshi,
Ya, i know the reason , i have mentioned parse date else null in my fuction. Thatswhy i got this error _Only the date between january 1 1753 and december 31 9999 are accepted.
( row1!=null)?TalendDate.parseDate("yyyy/MM/dd",row1.Date):null
How to solve this issue............
One Star

Re: How to convert string to date format!

Hi Mahadevan,
Please check out your format.
The format what you have specified is for the input column. So define the format in which the source is present.
You have specified yyyy/MM/dd, but you mentioned earlier that the source is in dd/MM/yyyy format.
Please try out
(row1.ExcelDateCol!=null)?TalendDate.parseDate("dd/MM/yyyy",row1.ExcelDateCol):null
instead of
(row1.ExcelDateCol!=null)?TalendDate.parseDate("yyyy/MM/dd",row1.ExcelDateCol):null
The yyyy/MM/dd should be specified in the date pattern of the target column and not in this formula.
Regards,
Karthikeyan
One Star

Re: How to convert string to date format!

Hi karthikeyan thank you for your response............
Here i clearly explain you , I have a Date column in my excel source in dd/MM/yyyy format.
Now i want to load all the entire rows of data to tmssqloutput, here i used tfileinput, tmap,tmssqloutput.
In source column : Date string
In tmap : (row1.Date!=null)?TalendDate.parseDate("dd/MM/yyyy",row1.Date):null (i used this formula in expression),
In output Smiley Very Happyate Date(yyyy/MM/dd).
One Star

Re: How to convert string to date format!

Hi Maha,
What i have shared is also a working model. Please check my input and output (in screenshot above)
I created a job to try it out and then posted the screenshot. I think your Excel sheet has to be analyzed.
Since your output is MS SQL, can you just try MM/dd/yyyy as well?
What is the date pattern available in tMSsqlOutput?
Or what I would suggest is, remove the MSSQLOutput and put only tLogRow and check whether there is same error coming. If not, then only problem is with the output and check the data pattern in the output. If still problem exists, then try to remove few data from the Excel and check out where the problem really exist.
Thanks,
Karthikeyan
One Star

Re: How to convert string to date format!

Hi any one knows how to set max value(Date ) to context variable for each job run .................if yes than kindly provide me the solution
My source is excel and i created context variable with some date value to filter the data for incremental load logic for tmssqloutput