What is the most efficient way to deal with different date formats in the same column?

[SGolnik]

RegEx isn't my strength and a similar issue prevented me from finishing my Designer Cloud Core Cert exam. Now that I have figured out one way to accomplish this, I really want to figure out if there is a better way. If I had a string column that contained two different types of date formats and I wanted to change them to the same format, I am wondering what the most efficient way to do this is? For example, let's say the column had the data type of string and contained dates formatted like the example below and I wanted them all in the Alteryx standard of yyyy-MM-dd. I accomplished this by extracting one of the date formats to its own column as an array, extracting the value, and then by changing the data type and format. For the date left in the original column: I changed the data type of the column to date, then changed the date format. The last step was with a formula to replace the null values in the original column with the dates from the other date column. It worked, but was a lot of steps and created a couple of extra columns that had to be deleted. Thank you in advance for any assistance!!

Date

2015-09-15

September 18, 2018

2018-09-01

November 27, 2020

Date	Date split	Date split1
2015-09-15	[]
	[September 18, 2018]	2018-09-18
2018-09-01	[]
	[November 27, 2020]	2020-11-27

[JORGE4900]

Hi @SGolnik 

I love Regular Expressions; here is my take on that approach.

I would first start by identifying the any first pattern on the main column and use the parse method to create a new column. It would be followed by identifying the second pattern. You could optionally parse both patterns in the same column. You could use the replace method, but it would just put the same values in the same column.

If you wanted to take it a step further, you could use the formula tool and create new columns for the different patterns or use an If Else statement to parse both patterns into the same column.

Spoiler

[SGolnik]

Thank you for your reply @JORGE4900 ! Your screen shots/workflow didn't help because I am trying to do this in designer cloud, not traditional designer, but your methodology did give me a little be more insight into how I can streamline things a little bit. I'm still struggling though if there is a way to extract and change the format of a date in a single transformation.

[SGolnik · Accepted solution]

I really was making this way too hard!!! With enough clicking around, I stumbled upon the easiest way of dealing with this situation in Designer Cloud and thought I would share for anyone else who might have this same issue. First, make sure the data type of the column is set to Date/Time using the format of the most common date format in the column. Then hitting the drop down in the top right of the column just select the option for Standardize. If you already did the first step, you can select the "auto-standardize" method and.....BAM....your data is all now in the same format. Then it would just be a transformation to change it to whichever date/time format you need it to be in, if it isn't already in it.

[JORGE4900]

Hi @SGolnik 

Looks like I missed the part that it was for Designer Cloud, but I am glad you found a solution for it.