One Star ply
One Star

[resolved] Converting String to Date

I have a field that looks like a date but comes in as a string (following a MM/dd/yyyy pattern). Some of them are missing or show as 00/00/0000. I've tried working through this using variations and combinations of parseDate, isDate, and formatDate but can't seem to find the right combination. Schema on the original data is a string, but the results is set as a date. I want to convert the date from a string to a date in a yyyy-MM-dd format when there is a valid date, but it want it to be a null value if it is an invalid date (including 00/00/0000). I think this should be fairly simple, but I'm new to Talend and Java so I can't seem to figure it out. Any help would be very much appreciated.
1 ACCEPTED SOLUTION

Accepted Solutions
One Star ply
One Star

Re: [resolved] Converting String to Date

I had to clean up my settings, but then it worked like a charm! Thank you!!!
I had set up the input data as metadata for the schema, but when I went to make the changes to string/date type as you suggested, I edited them in the Schema editor in the tMap component and I think that screwed things up. I made the correction to the metadata and re-ran your code. I still had problems until I changed my output for the dates to the date type and then it was perfect.
7 REPLIES
Seventeen Stars

Re: [resolved] Converting String to Date

At first a real Date does not have a pattern, only the string presentation has a format (also known as pattern).
I would read the fields at first as String typed column. After that I would use a tMap and in the Variable section (in the middle) I would use this expression:
TalendDate.isDate(row1.datestring,"MM/dd/yyyy") ? TalendDate.parseDate("MM/dd/yyyy", row1.datestring) : null

The output (from the tMap) must be of type Date.
One Star ply
One Star

Re: [resolved] Converting String to Date

When I go to test I'm still getting the error: "Exception in thread "main" java.lang.Error: Unresolved compilation problem:" and the NULL at the end of the code gives me the error: "Syntax error, insert ": Expression" to complete Expression". Thanks for the help!
Seventeen Stars

Re: [resolved] Converting String to Date

My expression it self could not be the problem, I have tested it in my studio and it works.
Could you please post a screenshot where you use my expression. This expression was made for the tMap variable expression. It should work.
One Star ply
One Star

Re: [resolved] Converting String to Date

I tried your code again and this time I got a test result of null which is what I expected, but there is an issue with isDate and parseDate where "The method parseDate(String, String) in the type TalendDate is not applicable for the arguments (String, Date)". I tried uploading the screen shots earlier and it wouldn't let me post - possibly because I don't have 10 posts yet?
Seventeen Stars

Re: [resolved] Converting String to Date

Yes it is true. The method parseDate expects 2 strings: 1. string is the string representation of the Date and the 2. string is the pattern. Please check the datatype of the column containing the problematic date values and take care it is of the type String and not Date because the build-in Date conversion in the input component fails for invalid date values. Thats why we get the date value at first as string (which cannot fail) and than check this value to ensure a valid Date value and THAN converting it into a Date !
One Star ply
One Star

Re: [resolved] Converting String to Date

I had to clean up my settings, but then it worked like a charm! Thank you!!!
I had set up the input data as metadata for the schema, but when I went to make the changes to string/date type as you suggested, I edited them in the Schema editor in the tMap component and I think that screwed things up. I made the correction to the metadata and re-ran your code. I still had problems until I changed my output for the dates to the date type and then it was perfect.
Seventeen Stars

Re: [resolved] Converting String to Date

Learning by doing ;-)