Convert Multiple String Dates to Date Time Simultaneoulsy

[hellyars]

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?

Date1	Date2	Date3	Date4
11/02/1387	06/05/1232	12/31/1931	01/01/2064

[atcodedog05 · Accepted solution]

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")

Output:

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

https://help.alteryx.com/current/designer/datetime-functions#:~:text=Designer%20cannot%20process%20d....

Hope this helps 🙂

[hellyars]

@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. 

[danilang]

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

Dan