Search code examples
datedatetimetalend

Converting String to Date using T-map with Talend Open Studio

I'm having trouble converting a couple of sting fields to date with Talend Open Studio. All date fields are coming to me via csv in the format "MM/dd/yyyy hh:mm:ss aa" For example "03/20/2018 2:40:03 PM"

Many date fields are fine, and conversion with the t-map component and the talend.date (parse date) is working as it should. The problem occurs in two fields where many almost all of the dates are coming in as "12/30/1899 00:00:00 AM"

I'm using the tmap to push the file to a toutputdelimited (CSV). The format of the fields with "12/30/1899 00:00:00 AM" is coming back as "#######################." Any ideas on how to handle the situation would be great. The output date format is "yyyy-MM-dd HH:mm:ss"

Again, all other fields are converting as they should. Its only the special case "12/30/1899 00:00:00 AM" that is causing problems.

Thans!

Solution

  • Since your dates can be of different formats, you need to read your column as a String type in your tFileInputDelimited, then inside a tMap, check the date type (a simple way to check it is by testing its length) and parse it using the correct date format:

     tFileInputDelimited -- tMap -- output

    In a tMap expression, you can have something like this :

    row.created_ts.length() == 22 ? TalendDate.parseDate("yyyy-MM-dd hh:mm:ssX", row.yourdate) : TalendDate.parseDate("yyyy-MM-dd hh:mm:ss.SSSSSSX", row.yourdate)