What is the best way to filter different Date formats in a column?

[AKPWZ]

Hi,

Can someone please help me on this - A column with different Date formats needs to be filtered. How do I do that & convert it to a right right DateTime format?

Using the '/', '-' or '.' symbols can be used for the "Contain" function in the filter tool, but I want something dynamic rather than hard coded.

Attached sample data.

Thank you!

[ShankerV]

Hi @AKPWZ 

What is the expected output please.

[AKPWZ]

Hi @ShankerV the expected output should be  Date format "2021-10-21". Thank you

[binuacs · Accepted solution]

@AKPWZ Since the date fields are in different formats the only option is to identify the patterns using the symbols you mentioned above or using Regex function

[SeanAdams]

Hey @AKPWZ 

As @binuacs says - the most common way to do this is with Regular Expressions.    Regular expressions (regex) are a pattern matching framework used across many different software languages and they have been around for ages - they are a little quirky to learn.

The good news is that there's a good bit of training in the Academy (under Parsing I believe).

The general pattern that @binuacs is pointing to is:

- if it looks like 4 digits; followed by a dash; followed by 2 digits - then apply 1 method.    In regex this is written as \d{4}\-\d{2} since \d is a digit and {4} is the number of the prev pattern you are looking for

- if it looks like 4 digits followed by a slash followed by ...

You can do this in a formula, you could also add a bunch of filters, where each filter just looks for one of these patterns using Regex Match.

Regex is a very good skill to have - and with Chat GPT and other similar tech - you can now learn regex very quickly - and my go-to for regex is http://regex101.com

Good luck - hope this helps

Sean

[AKPWZ]

Hi @SeanAdams thank you so much for encouraging me.

[JerrySmith]

I leveraged the above examples to expand upon converting different formats to conform to the Alteryx standard.   It doesn't yet validate that the date is valid.  I had a file that had multiple formats (m/d/yy, mm/dd/yyyy, yyyy-mm-dd, etc).  I hope this helps someone! 

[binuacs]

@JerrySmith Made a few changes in your workflow and attached

[AKPWZ]

Hi @binuacs  thank you so much for the update.
Just wanted to know one more thing that what change should we make in the RegEx to get the output as in DateTime format?

Like this, 2023-10-03 19:23:40

Thank you

[binuacs]

@AKPWZ Change the data type to DateTime, in the above example it was a V_WString