How to manipulate different date time from string format

Hi @Kenyx, based on your original post, as @bruno29a has mentioned, you showed both d-m-yyyy and m/d/yyyy formats, so I’m going to make the assumption that if the date uses / separators, then it is m d yyyy, but if it uses - separators then the day comes first.

The attached flow will handle all the date formats you have given so far, and can be extended (or changed) to handle others by modifying the Rule Engine.

e.g.
image
where as per your post, 1-8-2021 is 1 August 2021, whereas 1/10/2020 is January 10 2020 (because it uses / symbols)! :wink:

So the dates are translated as follows:
image

It uses a rule engine to define the date format to be used based on the pattern presented

Where there is no 4-digit sequence found in the date, it assumes there is no year and appends 2019 to the end of the datevalue string

The rule engine identifies the date format mask to be applied for conversion. single and double digit days and months are handled automatically by simply specifying d/M or M/d in the format masks as appropriate.

Because the String to Date&Time format mask is either configured, or dynamically supplied by a flow variable, in order to use a different mask for each row, it must be processed inside a loop that creates a different value in the flow variable for each iteration.

An alternative is to use a Java Snippet to perform the same task. Within the Java Snippet, the format mask can be supplied via a column value. This would be noticeably faster on large data sets.

The attached flow provides both the loop and a Java Snippet variant using the same input data.

KNIME_Date_Handler.knwf (39.6 KB)

7 Likes