topic Re: Date Problem in Talend Studio

Date Problem

Anonymous — Wed, 29 Jan 2014 12:09:52 GMT

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.

Re: Date Problem

Anonymous — Tue, 04 Feb 2014 10:49:37 GMT

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

Re: Date Problem

Anonymous — Tue, 04 Feb 2014 11:12:22 GMT

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.

Re: Date Problem

Anonymous — Tue, 04 Feb 2014 13:57:32 GMT

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

Re: Date Problem

Anonymous — Wed, 05 Feb 2014 05:55:09 GMT

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.

Re: Date Problem

Anonymous — Fri, 07 Feb 2014 02:09:46 GMT

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

Re: Date Problem

Anonymous — Wed, 05 Mar 2014 06:49:15 GMT

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.

Re: Date Problem

Anonymous — Wed, 05 Mar 2014 07:51:07 GMT

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

Re: Date Problem

bkar81 — Wed, 05 Mar 2014 09:30:42 GMT

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

Re: Date Problem

Anonymous — Wed, 05 Mar 2014 09:43:47 GMT

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.

Re: Date Problem

bkar81 — Wed, 05 Mar 2014 09:47:40 GMT

Change the format to
(row1.Anniversary_Date!=null)?TalendDate.parseDate("MM/dd/yyyy",row1.Anniversary_Date):null
Check the target DB Datatype in the target table in tMap component. By default it will be dd/MM/yyyy. Change it to MM/dd/yyyy. It will ask for propagate changes. Accept it.
Check the same in the MSSQLOutput column as well.
Let us know if it works.

Re: Date Problem

Anonymous — Wed, 05 Mar 2014 10:17:51 GMT

Hi
still the problem is there.i have attached the error along with this post. My date column has some null also.i need all the rows to be loaded irrespective of null or not null. give me a solution.
Thanks & Regards,
Arnold.

Re: Date Problem

bkar81 — Wed, 05 Mar 2014 10:46:10 GMT

Check this out
in tMap, issue this
(row1.Anniversary_Date!=null)?TalendDate.parseDate("dd/MM/yyyy",row1.Anniversary_Date):null
Check out the pics below

Re: Date Problem

Anonymous — Fri, 07 Mar 2014 09:01:35 GMT

Hi
still it is showing the same error as i said in my first post i have few date column like DOB,Anniversary date,Contract Expiration date,Hire date,Leave date in my excel sheet.Except the DOB and Hire date rest of the date columns have some null values,each of the column has different data types. So i need all the rows in all these date columns to be taken in to the target system.
And another thing is...
For example if we take the column DOB, in it one row has Date format and another row has string format thus in a single Column DOB, i have multiple rows that have multiple formats like Date,string etc
Example representation
DOB
****
12/01/2001 ---> date datatype
02/08/1988 ---> String datatype
05/1/2007 ----> String data type
02/05/2013 ----> date datatype
considering all this, how to bring a solution???

Regards,
Arnold.

Re: Date Problem

bkar81 — Fri, 14 Mar 2014 09:25:10 GMT

Since you have both formats in the same column, while loading it, make it a string column and do an explicit type conversion to date

Re: Date Problem

Anonymous — Fri, 14 Mar 2014 12:57:59 GMT

Hi,
can you explain to me in detail that how to do it?

Thanks & Regards,
Arnold.

Re: Date Problem

bkar81 — Fri, 14 Mar 2014 14:39:57 GMT

Please find the screenshots below
While loading, I set the datatype of the date column to String.
Image 1: The input data in Excel (string & date format) - you can see by the difference in alignment
Image 2: The initial mapping to parse the data from Excel
Image 3: Oops, an error occurred - Unable to parse the date (Note the highlighted date format)
Image 4: Analyze why it is throwing the error (removed the tMap and directly connected to tLogRow)
Image 5: Got it, there is the problem... few items are parsed properly and few not parsed properly
Image 6: A slight tweak in my code in tMap - used SimpleDateFormat (Java code), but we can also use TalendDate.parseDate
Image 7: Viola!!! All dates are parsed properly.
Formula used inside tMap
(row1.DateCol_InString!=null)?(TalendDate.isDate(row1.DateCol_InString,"dd/MM/yyyy")?
TalendDate.parseDate("dd/MM/yyyy",row1.DateCol_InString): new SimpleDateFormat("EEE MMM dd hh:mm:ss z yyyy"). parse(row1.DateCol_InString)):null
or
(row1.DateCol_InString!=null)?(TalendDate.isDate(row1.DateCol_InString,"dd/MM/yyyy")?
TalendDate.parseDate("dd/MM/yyyy",row1.DateCol_InString): TalendDate.parseDate("EEE MMM dd hh:mm:ss z yyyy",row1.DateCol_InString)):null
I checked whether the date I am parsing is in the desired format using TalendDate.isDate function
If it returns true, then parse normally, else do the custom parsing.
Here you go:
For Date in the format = "Wed Jan 01 00:00:00 GMT 2014", I used the format "EEE MMM dd hh:mm:ss z yyyy"
So check out the same and find out which format the output is coming.
In a nutshell, use isDate and parseDate in multiple if conditions depending upon your need.
Note: If the destination server is in different locale, this would not work (example, the above example is in UK format. This may not be the same if I execute this in other machine where the date settings are different)