Change date format

[Pedro87]

Hello
I want to thank the community for kindly answering previous queries.
I submit the following query:
I have a field called FEC_VENCIMIENTO which is of type string and its content is a date in yyyy-mm-dd format, where yyyy is a year, mm is a month and dd is the day, for example 2016-05-31.
What I need is to convert the yyyy-mm-dd format to dd-mm-yyyy, leaving 31-05-2016 for the same case.

Attached screen print

[ArjenEX]

Hi @Pedro87

This topic has been covered frequently on the forum and elsewhere. For example, this blog is very extensive on date handling

Explore Using Date&Time Formats in KNIME plus Best Practices | KNIME

To your case, you can approach it from multiple directions. One would be convert it to a date instance, and then convert it to your desired format.

Another one would to just directly extract the year, month plus and join it together (through Column Expression, String Manipulation)

join(substr(column("FEC_VENCIMIENTO"),8 ,2),"-",substr(column("FEC_VENCIMIENTO"),5 ,2),"-",substr(column("FEC_VENCIMIENTO"),0 ,4))

etc.

[Pedro87]

Thank you very much @ArjenEX, the described formula was useful to me occupying the expression column node.
I also read the blog you shared with me, and there are some very useful nodes that are better suited to other analysis cases.

[bruno29a]

@ArjenEX @Pedro87 , that’s a good way to do this, but bear in mind that this will work only for input dates of yyyy-mm-dd, which is the case here.

A more robust and more generic way is to apply this after the String to Date&Time and with the Extract Date&Time Fields node.

First of all, I want to make sure that @Pedro87 understands that Knime does not output dates in different date formats, therefore if you want to change the format, the result will be a string type, not a date type.

Something like this will allow you to process any date format without having to change the logic:

Input:

Output:

Logic used is this (I’m using String Manipulation):

join(padLeft(string($Day of month$), 2, "0")
    , "-", padLeft(string($Month (number)$), 2, "0")
    , "-", string($Year$)
)

And this will work for this input too:

You just need to specify what date format the input is in the String to Date&Time, and nothing else:

You get the same expected results:

This works because the Extract Date&Time Fields will extract that correct values of year, month and day following the specified format for String to Date&Time. I don’t have to change the logic that I showed above.

Here’s the workflow:

Change date format to dd-mm-yyyy – bruno29a

EDIT: @ArjenEX , sorry I missed your first option, I thought it was just a “demo” to swing between dates and string for @Pedro87 , and thought your solution was what you suggested in the Column Expression.

I’ve now realised that the String to Date&Time + Date&Time to String was actually a solution (I was playing around on my side and also came up with this), and a brilliant one actually.

You should share your workflow so we can see what’s really happening there

[Pedro87]

Hello @bruno29a good morning, I want to thank the support for: the functional understanding of knime and for the transversal solution.

Unfortunately I can’t upload my workflow since it weighs 14 gb and it doesn’t allow me to share it here