String to Date from 12DEC22 to 12.12.2022

[Shadi]

Hi All,
I am new to Knime and have the following question
I have the following date format as a string “12DEC22” that I want to change to 12.12.2022.
Is there any way to do that in knime?

Thanks

[takbb]

Hi @Shadi and welcome to the KNIME community

Ordinarily for this type of question, I would point you straight at the String to Date&Time node, and tell you that you just need to use a particular format mask.

For your particular data, however, it is unfortunate that converting it to a date is not quite as straightforward as it really ought to be, because the String to Date&Time node which would normally be used for this conversion uses the strict Java conversion, which is case-sensitive, according to different Locales. For example the US and GB locales en-US and en-GB both require the abbreviation for December to be “Dec” rather than “DEC”, and so attempting to convert will fail.

You could do this using two nodes. The first being String Manipulation with the following code:

join(substr($column1$,0,2),capitalize(substr($column1$,2)))

where $column1$ would be replaced by your column reference. This “capitalizes” (Makes capital first letter followed by lower case) the portion of the string containing the month onwards.

After that, the String to Date&Time node can convert to Date using a format mask of dMMMyy.

Alternatively… It was for cases such as this that I added a component to the KNIME hub, which could also perform this conversion very simply.

Parse Date&Time – takbb

If you open that link and then drag the Parse Date&Time icon onto your workflow, you simply configure it as follows:

Here is a workflow demonstrating both of the above methods

Date Conversion with upper case month – takbb

[Shadi]

Thanks a lot. It works now