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.

[bkar81]

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.

[Anonymous]

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.

[bkar81]

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

[Anonymous]

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.

[bkar81]

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

[Anonymous]

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

Thanks & Regards,
Arnold.

[bkar81]

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)