One Star

I am trying to make an invalid date null when writing to a txt file.

I am encountering a problem with transforming a date in expression builder. I have a date field called birth_date from an incoming text file that is in the format of 'yyyy-mm-dd'. I set the column type as a string length of 10 in the metadata. I setup a variable using the expression below between the row1.birthdate column and the output row1.birthdate column. The expression that I am using in the Var expression builder to the string as a date if the month is not 00- is :
TalendDate.getPartOfDate("MONTH",TalendDate.parseDate("yyyy-MM-dd", row1.Date_of_Birth)) <= 0 ? null : TalendDate.parseDate("yyyy-MM-dd",row1.Date_of_Birth). It doesn't seem to work for me. I still have dates being written to my output file as 1985-00-12 for example. I also tried using the isDate function like this:
TalendDate.isDate(TalendDate.formatDate("yyyy-MM-dd HH:mm:ss",row1.Date_of_Birth),"yyyy-MM-dd HH:mm:ss") == true ? TalendDate.formatDate("yyyy-MM-dd",row1.Date_of_Birth) : null
It didn't work either. The invalid date still comes out as 1985-00-12 instead of being null in the record. Has anyone experienced this and how do I fix it?
3 REPLIES
Five Stars

Re: I am trying to make an invalid date null when writing to a txt file.

Your issue is that parseDate is being lenient with the String you're passing. Month=00 is being taken as December.
Take a look at https://jira.talendforge.org/browse/TDI-3773
It's old; but give some insight to the issue.
One Star

Re: I am trying to make an invalid date null when writing to a txt file.

I am new to Talend and Java coding. I built an expression in the target field to check the validity of a data string. The logic is if the string is a data write it to the target and if it is not a date, then make it null. I've used the Expression Builder tester to verify that my transformation logic works. The Expression Builder Tester shows me the date if it is a valid one like 1987-05-21 and returns a null if the date is 1987-05-39. However when I execute the job, I see valid dates like the 1987-05-21 example, but also have the invalid dates like the 1987-05-39 example. I am expecting a null value for the date field in the record is the date is invalid similar to 1987-05-39 or 1987-00-28. What would cause Talend not to use the transformation to validate the date field? Here is an example of the transformation that I am using by the way... (TalendDate.isDate(row1.Date_of_Birth,"yyyy-MM-dd") == true) ?TalendDate.parseDate("yyyy-MM-dd",row1.Date_of_Birth):null Anyone have a solution to the problem I am experiencing?
Five Stars

Re: I am trying to make an invalid date null when writing to a txt file.

I can't see an issue with the logic you've posted here.
System.out.println("1987-05-30 is a date? " + TalendDate.isDate("1987-05-30", "yyyy-MM-dd"));
System.out.println("1987-05-39 is a date? " + TalendDate.isDate("1987-05-39", "yyyy-MM-dd"));
System.out.println("1987-00-28 is a date? " + TalendDate.isDate("1987-00-28", "yyyy-MM-dd"));
System.out.println( (TalendDate.isDate("1987-00-28","yyyy-MM-dd") == true) ? TalendDate.parseDate("yyyy-MM-dd","1987-00-28"):null);
1987-05-30 is a date? true
1987-05-39 is a date? false
1987-00-28 is a date? false
null