[resolved] Null Invalid Dates in Tmap

Anonymous — Thu, 03 Nov 2016 16:41:50 GMT

I have an excel file where records with invalid dates is common or are populated with N/A, no data, etc. I would like to null out the invalid DOB rather than reject/filter out the entire record. The date is coming in a string in the file and I am trying to insert into a date field in oracle.
I have tried formatting the expression a couple of different ways but I still get an error rather than a null DOB record. I would expect these to come through as null as opposed to throwing an error. What am i doing wrong?
The string date looks like:
Fri Apr 03 00:00:00 PDT 2009 or Fri Apr 16 00:00:00 PST 1982 or 3/29/1969
1. (TalendDate.isDate(row12.Patient_DOB, "EEE MMM dd HH:mm:ss zzz yyyy") == true) ? TalendDate.parseDate("MM/dd/yyyy",row12.Patient_DOB):null
--when i use this i get an error on Unparseable date: "Tue Jun 06 00:00:00 PDT 1967"
2. (TalendDate.isDate(row12.Patient_DOB, "EEE MMM dd HH:mm:ss Z yyyy") == true) ? TalendDate.parseDate("MM/dd/yyyy",row12.Patient_DOB):null
--when i use this format I get an error on Unparseable date: "3/29/1969"
3. (TalendDate.isDate(row12.Patient_DOB, "MM/dd/yyyy") == true) ? TalendDate.parseDate("MM/dd/yyyy",row12.Patient_DOB):null
--when i use this format I get an error on Unparseable date: "3/29/1969"
Any help is greatly appreciated!
Thanks!

Re: [resolved] Null Invalid Dates in Tmap

vapukov — Fri, 04 Nov 2016 00:00:47 GMT

You not fully correct parse dates (see attached) - need update if-lese-if-else logic and parse date must use same format as source date string
one more - I You also need handle short format - 3/29/1969 - not parsable, but 03/29/1969 - parsable
sure it possible to do by different ways, I just check length and add 0 if omitted

Re: [resolved] Null Invalid Dates in Tmap

Anonymous — Fri, 04 Nov 2016 03:18:03 GMT

that worked perfectly thank you!

topic Re: [resolved] Null Invalid Dates in Tmap in Talend Studio

[resolved] Null Invalid Dates in Tmap

Re: [resolved] Null Invalid Dates in Tmap

Re: [resolved] Null Invalid Dates in Tmap