Date Problem

[Anonymous]

Hi
Am new to talend.....I need a little help with this issue am going through...

I have an issue with my date format...when I pull my excel file into talend which contains the dates in different formats,it throws errors while transferring the datas into tMSSqlOutput . Am not able to get all the rows in my file.I just want to transfer the entire data along with the dates in the tFileinputExcel file to tMSSqlOutput with the date format in dd/MM/yyyy.
I need to do this without changing the date format in my original excel file. how to do this in talend?
I have attached the images....
Please do help me
Thanks & Regards,
Apk.

[Anonymous]

Hi
Read this column with String type, then parse the value to a Date based on the format, for example:
tFileInputExcel--row1--tMap--row2--tMssqlOutput
on tMap, parse the value to a Date:

TalendDate.isDate(row1.c1,"yyyy-MM-dd")?TalendDate.parseDate("yyyy-MM-dd",row1.c1):(TalendDate.isDate(row1.c1,"dd-MM-yyyy")?TalendDate.parseDate("dd-MM-yyyy",row1.c1):TalendDate.parseDate("MM-dd-yyyy",row1.c1))

Shong

[Anonymous]

Hi Shong,
Thank you!!
I also have another problem as my date columns contains null values to it. The sqlserver throws error. How to solve it and send the entire date column to the target.
Thanks & Regards,
Arnold.

[Anonymous]

Hi
What's the value do you want to replace if the value is null? filter this row or set it with a default date if it is null?
Shong

[Anonymous]

Hi,
I would like to replace the null column with 0 or some text like "Date not available". Need to know how to do it for both the option

Thanks & regards,
Arnold.

[Anonymous]

If the data type of column is datetime in your table, only dates between January 1, 1753 and December 31, 9999 or Null are accepted, so, you can set it with Null or a default datetime if the input data is null, for example:
row1.c1==null?null TalendDate.isDate(row1.c1,"yyyy-MM-dd")?TalendDate.parseDate("yyyy-MM-dd",row1.c1) TalendDate.isDate(row1.c1,"dd-MM-yyyy")?TalendDate.parseDate("dd-MM-yyyy",row1.c1):TalendDate.parseDate("MM-dd-yyyy",row1.c1)))
Shong

[Anonymous]

Hi,
am having a trouble with this date format. I need my date to be in dd/MM/yyyy format (date alone no time).My date column is in string format in my excel sheet some of the rows in my date column are empty or null. Am able to change the format in Tmap from string to date by using the following code

CODE :
(row1.Anniversary_Date!=null)?TalendDate.parseDate("dd/MM/yyyy",row1.Anniversary_Date):null
the output runs in tLogRow and shows all rows,but when I pull the data into sqlserver 2008 it shows date format error.
I also have another problem as my date columns contains null values to it. The sqlserver throws error. How to solve it and send the entire date column to the target.
I have attached my work and error in this post.

please help me to resolve this issue.

Thanks & Regards,
APK.

[Anonymous]

Hi,
Please check the library of Sql Server Date and Time Data Types and Functions(Transact-SQL) http://technet.microsoft.com/en-us/library/ms186724.aspx. The datetime range is "1753-01-01 through 9999-12-31".
Best regards
Sabrina

[bkar81]

MSSQL will not allow inserting date of type dd/MM/yyyy.
Please change the format to MM/dd/yyyy and try it out.
Note: You have to change the format both in the formula and the datatype as well...

[Anonymous]

Hi,
thank you bkar81 & xdshi.
But what is the solution to make the date column to get into the target (MSSQL)???
bkar81 can u brief the solution by how to apply the change in both the formula & datatype as well

Thanks & Regards,
Arnold.