Different Date Formats

[Vivaldi]

Hello Dear Community,

i ran into a problem at my workplace. There is a file in which i need to compare two date columns, so obviously they have to be the same format.
Now, one of them has two different formats: yyyy-mm-dd and dd-mm-yyyy.
I have no idea how i can change the column so that every cell has the same format.

Does anyone has an idea or any suggestions?

Dear regards,

Vivaldi

[johannes.schweig]

Hey @Vivaldi,
in KNIME there is a data type called Date&Time that you can use for that. If you use the String to Date&Time node, it will convert you columns to Date&Time and then you can sort them, filter them etc.
Cheers,
Johannes

[Vivaldi]

Hello Johannes Schweig, thank you for your fast reply!

I already tried this node actually but to no avail.
Iit gives me an error that it couldnt convert it one way or another; so from yyyy-mm-dd to dd-mm-yyyy or vice versa.
maybe the pictures explain my problem a bit better

any help is highly apreciated

[qqilihq]

@Vivaldi

Now, one of them has two different formats: yyyy-mm-dd and dd-mm-yyyy.

You could give Palladian’s Date Extractor node a try. It has several parse patterns built-in and will automatically try to select the appropriate one:

Palladian — NodePit

[Vivaldi]

hello there!

thanks for the tip but I’m afraid i wont be able tio use this because i’m in a corporate environment…
any tips on where i can find a how-to install?

[qqilihq]

Hi Vivaldi,

if you’re permitted to do so, you’ll simply will need to add the update site on the page to KNIME (i.e. http://download.nodepit.com/palladian/4.0) the preferences under “Software Sites“, and then install “Palladian” via “Install Plugins and Extension”.

Best,
Philipp

[Tazka]

Hey Vivaldi,

Perhaps you could use the Row Splitter node

then use the String to Date&Time node for both splits separately. You can concatenate them afterwards. So your workflow would look like this:

BR,
Tazka

[ScottF]

Hi @Vivaldi -

The reason the String to Date&Time node is failing is because of the date format you have specified. In your screenshot, the Date format field is populated with dd.MM.yyyy. According to the contents of your first cell, you should change this text instead to yyyy-MM-dd.

As Johannes mentioned, once you parse the dates into KNIME’s native Date format, then you can do all kinds of options on them very simply using other Date&Time nodes.

[armingrudd]

Hi @Vivaldi,

Use a String Manipulation node to transform the date format from dd-mm-yyyy to yyyy-mm-dd. So all the values inside the column will have the same format. You need to use this expression inside the String Manipulation node:
regexReplace($column1$, "([\\d]{2})-([\\d]{2})-([\\d]{4})", "$3-$2-$1")

Where “column1” is the name of the column with 2 different date formats.

Now you can use the String to Date&Time node to convert the column type to Date&Time.

string_to_date.knwf (286.5 KB)

[Vivaldi]

Hello everyone,

Thank you all for your responses!!!
i’ll try the row splitter and string manipulation node later, and @qqilihq unfortunately i cant install that because of my companies network configuration

thank you all for your suggestions, I’ll report back as soon as i can

[Vivaldi]

Alright, the row splitter worked like a charm!
I Had to add the option “Contains Wildcards” to make it work, but after that all it took was reformatting and a simple concatenate as @Tazka suggested

thank you very much for your help and have a nice day