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 | |
Solved! Go to 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
Hope this helps 🙂
@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.
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