[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
Forteen 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
Forteen 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!

What’s New for Talend Spring ’19

Watch the recorded webinar!

Watch Now

Definitive Guide to Data Quality

Create systems and workflow to manage clean data ingestion and data transformation.

Download

Tutorial

Introduction to Talend Open Studio for Data Integration.

Watch

Downloads and Trials

Test drive Talend's enterprise products.

Downloads