How to convert string to date format!

[Anonymous]

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

[Anonymous]

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

[Anonymous]

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

[Anonymous]

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

[Anonymous]

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

[bkar81]

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

[Anonymous]

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

[bkar81]

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

[Anonymous]

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 ate Date(yyyy/MM/dd).

[bkar81]

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

[Anonymous]

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