Community Spring Cleaning week is here! Join your fellow Maveryx in digging through your old posts and marking comments on them as solved. Learn more here!

Alteryx Designer Desktop Discussions

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

Convert Multiple String Dates to Date Time Simultaneoulsy

hellyars
13 - Pulsar

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
13 - Pulsar

@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
19 - Altair
19 - Altair

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