String to date format: 21MAR2025

[versatilka]

Dear Community, any idea how to transfer the string date 21MAR2025 to date format?
Thank you very much.
Pavla

[Thyme]

Hello @versatilka and Welcome to the KNIME community!

To parse this String into a date, we first need to properly format it so the String to Date&Time node understands it:

1. Insert separators between the date fields, using a RegEx replace with substitution
2. Change capitalisation for the month field
3. cast String to Date with this format "dd.MMM.yyyy" and Locale "en-US"
   If your day field does not have leading zeros, you only need one “d”. If it doesn’t work, you might need a different Locale.

This is the RegEx used:

regexReplace($column1$, "^([0-9]+)([a-z,A-Z]+)([0-9]+)$", "$1.$2.$3")
^         start of line
$         end of line
+         one more or of the preceeding character
[0-9]     any digit
[a-z,A-Z] any letter
()        anything within round brackets can be referenced as variable
          variables are denoted with $ and the number of its appearance

Don’t forget to mark a post as solution!

string to date.knwf (19.4 KB)

[bruno29a]

Hi @versatilka and welcome to the Knime Community.

The format MMM for month supports the format of Jan, Feb, Mar, etc for month, which is basically the capitalized version of what you have (MAR vs Mar). So you can just capitalize the month via the String Manipulation like this:
join(substr($column1$, 0, 2), capitalize(substr($column1$, 2, 3)), substr($column1$, 5))

This should give you 21Mar2025

You can now use the String to Date&Time node to transform this into a date type using the Date format ddMMMyyyy

Something like this would work:

String Manipulation:

String to Date&Time:

EDIT: Sorry, forgot to attach the workflow. Also, the example that you gave (21) does not show if the day is a 2 digits representation or single (01 vs 1, 02 vs 2, etc), so I modified the String Manipulation to take care of this like this:
join(substr(padLeft($column1$, 9, "0"), 0, 2), capitalize(substr(padLeft($column1$, 9, "0"), 2, 3)), substr(padLeft($column1$, 9, "0"), 5))

Now it does not matter what representation the day is. It works for both. For example:

Results:

Here is the workflow, with the modified expression: String to date&time month name to format MMM.knwf (11.8 KB)

[versatilka]

@Thyme @bruno29a thank you both!! That was really helpful and it worked.