How to separate day, month and year when mm/dd/yyyy is not standardized

I am trying to seperate the day, month and year from the Date column but it is not standardized. It looks something like this


I cant extract it because the “day” format is not standardized. If the day is single digit it should be 08 instead of 8, is there any way to standardize it?

There are several methods that come to mind.

  1. Use the Cell Splitter node to split the cell using the slash as a delimiter, and take the results of the 2nd output column
  2. Use the RegEx Extractor node with the regex (?<=/)[0-9]{1,2}(?=/) to grab whatever is between two slashes
  3. Use the String to Date&Time node to convert to the KNIME Date&Time format using the format M/d/yy, then use the Extract Date&Time Fields node to grab the individual components you want.

There is an easy way to do this within the String to Date&Time node. Just use a conversion string of M/d/yy. Somewhat counterintuitively, this will actually deal with both single and double digit months and years. Try it and see!

EDIT: I see that @elsamuel already suggested this as #3 above. I didn’t read far enough :man_facepalming:

1 Like