[resolved] Null Invalid Dates in Tmap

One Star

[resolved] Null Invalid Dates in Tmap

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!

Accepted Solutions
Twelve Stars

Re: [resolved] Null Invalid Dates in Tmap

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 

    
-----------

All Replies
Twelve Stars

Re: [resolved] Null Invalid Dates in Tmap

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 

    
-----------
One Star

Re: [resolved] Null Invalid Dates in Tmap

that worked perfectlySmiley Happy  thank you!