Alteryx Designer Discussions

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

Convert Multiple String Dates to Date Time Simultaneoulsy

hellyars
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?

 

 

Date1Date2Date3Date4 
11/02/138706/05/123212/31/193101/01/2064 
     

 

3 REPLIES 3
atcodedog05
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")

 

Output:

atcodedog05_0-1621008346727.png

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

atcodedog05_1-1621008441667.png

 

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

 

Hope this helps 🙂

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

danilang
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

 

Dan

Labels