Alteryx Designer Discussions

Find answers, ask questions, and share expertise about Alteryx Designer.

Convert Multiple String Dates to Date Time Simultaneoulsy

12 - Quasar

I am trying to convert multiple string fields to date fields.


The dates start as mm/dd/yyyy (e.g. 10/31/20 and 05/04/21). 

I tried using a Multi Field tool and a regex_replace formula to re-arrange the date to yyyy-mm-dd for the selected fields using the following expression...




REGEX_Replace([_CurrentField_],"(\d{2})\/(\d{2})\/(\d{4})", "$3-$2-$1")




As a string, it works.  It outputs null if I try to re-arrange it and convert the output to a date.  Is there a way to do this without using multiple Date Time tools?





22 - Nova
22 - Nova

Hi @hellyars 


2 observations


1. There is a slight change you need to make to the formula. I have swapped 1 & 2 because the format is required Y-m-d but it was in Y-d-m. Please find the formula below. With this date 3 and date 4 is parsed.

REGEX_Replace([_CurrentField_],"(\d{2})\/(\d{2})\/(\d{4})", "$3-$1-$2")




2. Only dates after1400-01-01 is supported in Alteryx. Please refer the documentation page. Because of this date 1 & date 2 are NULLs



Hope this helps 🙂

12 - Quasar

@atcodedog05   Ugh.  I am getting killed by the stupid mistakes.  Time to take a break.  But, glad to know this works.  


Those were just random dates.  The real dates are all modern, but that is a bummer for the 14th century...some great plague data to map. 

18 - Pollux
18 - Pollux

Hi @hellyars 


No need to give up on pre-1400 dates!   You can still use the regex method to parse them, you just can't store them in a date field.  Luckily a String(10) field works fine and the yyyy-mm-dd format means that the values sort properly as strings as well.  Just make sure to left pad the year with "0" if you go early than 1000 AD