Change date format

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

Hi @Pedro87

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

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.

6 Likes

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.

1 Like

@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:
image

Input:
image

Output:
image

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:
image

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:
image

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:

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 :slight_smile:

3 Likes

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

This topic was automatically closed 7 days after the last reply. New replies are no longer allowed.