How to clean column with text and date values please?

This is my date_column


68-11-22, 68-11-20,68-11-20,99-9-3 are all stored as text values, but it should read as dd/mm/yyyy., such that in the case of 68-11-22, it should be 22/11/1968 How do I convert these to this format?
Also for those values that are stored as date, how do I filter such that I remove rows that have dates that are more than today’s date?

You can try this node

2 Likes

I am tagging @takbb because he once created this cool component which transforms various different spelled dates (If I remember correctly) Maybe he could link it here too in that case.
br

@Daniel_Weikert - something like this you mean? :wink:

(This is a new variant on my previous component :slight_smile: )

Hi @Omnidesker , and welcome to the KNIME community.

Handling date formats which are not standardised in a single column is “fun” :wink:

However, I have a component that may be able to assist. I suggest you give it a good test though, to make sure you are happy that it works for you.

By not having it fail on error, we can parse the same column twice, once with Day-Month-Year order and the other set to Year-Month-Day.

Set the “century” cutoff for two digit dates. I have just added that, so again it needs testing properly!!

After the second node, you have the dates in two columns,

image

which can then be merged, and tidied up

image

Fixing Dates.knwf (151.3 KB)

This is “hot off the press” so if you use it, please do let me know if you hit any snags and I’ll get back to you when I can.

[Edit
Of course depending on your data, if a two digit year is 31 or below, it is possible that year-month-day and day-month-year would both return a valid date.
e.g 13-11-20
Would that be 13 Nov 2020 or 20 Nov 2013 ?

You would have to decide which is the required date. You could maybe decide by writing a rule based on looking at the specific entered format such as if it contained “-” or “/” in the original date strings for example]

3 Likes

Seems I remember correctly. Great detailed explainations as well. Thanks for sharing
br and enjoy your weekend

1 Like

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