April 02, 2021 String to Date 02/04/2021 - How to convert string to date?

[theshreyansh]

Hi Knime Comminuty,
I got data as below, and want to change string to date, please guide

[bruno29a]

Hi @theshreyansh , your title says April 02, 2021, but the data you gave is April 1, 2020.

2 things here:

1. They’re different dates
2. More importantly, they’re 2 different format.

You see, the 1 would be of format d, and the 02 would be of format dd. You can’t have multiple formats when converting String to Date&Time.

I put something together for you, and I went with the format and date that you presented that is April 1, 2020.

Knime understand 1 as the day, 2020 as the year, but not April. The only thing that comes close to a name of month is the format MMM, which is basically the first 3 letters of the English name of the month, capitalized. So, basically, we want to modify April to Apr.

In my example, I added different dates with different months to show that it works with the different months.

Input dates:

Results:

Here’s the workflow:
String to date&time month name to format MMM.knwf (11.8 KB)

[takbb]

I feel that the help documentation that is supplied with the String to Date&Time nodes could do to be updated with a few more examples as this can be converted directly using just the Date&Time nodes, but it isn’t obvious.

MMMM as a format mask can handle full month names but it doesn’t state that, and whilst “dd” can handle only two digit day values, “d” as a format mask can handle both one and two digit day values. Again the documentation doesn’t explain that. I feel that there’s quite a lot about the date conversion nodes which is counter-intuitive and unexplained within the standard documentation for these nodes.

A format mask of

MMMM d, yyyy

can be used to convert April 1, 2021 and April 02, 2021

Whilst it is true that more than one format mask cannot be supplied, it is possible to include optional elements (using square brackets), so if your month data had been a mixture of abbreviated and full month names

the following
[MMMM][MMM] d, yyyy

would handle it.

@theshreyansh , from your question, you appear to be wanting to convert to a Date with format dd/MM/yyyy., and one thing to note is that to convert something from a date with one format to a date with a different specific format, you are actually needing to convert from String to Date and then back to String again, since a Date itself does not have an actual format of its own.

In the attached flow, I’ve configured the nodes to “append” the columns so that we can see the differing formats, but possibly in reality you would simply tell it to “replace” at each stage.

As you can see the intermediate “date” column is displayed in yyyy-MM-dd format. This is simply a function of the renderer and is how dates are displayed in KNIME. To change to a different format, it is necessary to convert it back to a string column of the required format. Ideally when using dates, the trick is to leave them as a Date column so that they can be manipulated and processed as actual dates and then convert them to a String at the point where you need them to be output in a specific format.

Convert date formats.knwf (9.4 KB)

[theshreyansh]

@bruno29a Many thanks for detailed explaination. thats really more than just help. thanks for guiding some of basic core things ‘one should know working on knime’

[takbb]

For more “fun” with date masks take a look at
KNIME_Workflow With flexible DATE format mask – KNIME Hub, and you can see a way to convert a wide variety of date strings to a Date using 3 nodes

[theshreyansh]

Woow… thanks for sharing goldmine @takbb …

[ipazin]

Hello @takbb,

know it comes as a shocker but have added your suggestions on improving String to Date&Time node’s description to existing ticket
(Internal Reference: AP-16865)

Br,
Ivan