Dear KNIME community,
I have only recently started using KNIME.
I have this difficulty:
in a csv file I have columns in the format dd-mmm-yyyy hh:mm:s for example “15-JAN-2022 16:25:18” text format and I would like to transform the value in “15-01-2022 16:25:18” date-time format.
Possibly replacing “-” with “/”.
Thank you for your time and help.
Hi @lriva , and welcome to the community!
The “simple” way to convert a string from one date format to another is by converting it first to a date, specifying the format that the string currently represents, and then converting it back to a string, specifying the required format. So typically this would be String to Date&Time node, followed by Date&Time to String node. It can be done using other nodes such as String manipulation but then you have to really work at writing what can turn out to be a relatively complicated (and error prone) script.
There is one little complication though with your date example. The date handling nodes in KNIME are based on the Java specification which is rather (in my view, unnecessarily) particular about how months are represented in your locale. For example if you are in US or UK, or other English-language locales, then the three letter month must be in Mixed case with an initial capital letter followed by lower case. It does not understand that JAN is the same as Jan, and fails to convert it.
So in your case, we need to do an extra piece of work to “capitalize” the month. This can be performed with the String Manipulation node, but once again there is a complication in that while it provides a “capitalize” function, it works on the string as a whole, and attempts to capitalize the initial character in each “word” in the string, rather than the first letter, and it appears it uses spaces to denote word boundaries rather than other punctuation.
To make it work for you, I therefore replaced the “-” with spaces before then “capitalizing” the date string.
Your initial date:
Is transformed to this:
Once that is done, it is relatively plain sailing provided that you know the java date/time format masks.
A date format mask of d MMM yyyy H:m:s will convert this into a DateTime object, and then after that, a Date&Time to String node can be used to convert it back to a string using either of these depending on which format you decide on (- or / )
dd-MM-yyyy H:m:s
dd/MM/yyyy H:m:s
or
Date format conversion.knwf (13.4 KB)
In this workflow, I’ve used a Table Creator for the sample data, but obviously you’d change that to be your CSV Reader and then join that to the String Manipulation node. You’ll need to change column name used in each of the nodes to match the column name in your data.
Hi @takbb
thanks for the tips!
Everything works perfectly !!!
I tried to make a modification-variant and that works too !
best regards
That’s great @lriva , glad you got it working for you and if you need more assistance there’s generally somebody more than willing to help you here on the forum! 
This topic was automatically closed 90 days after the last reply. New replies are no longer allowed.
