Hello Experts:
In a previous post, I received some assistance to properly convert a string into a valid date format. See question/solution @ https://community.alteryx.com/t5/Alteryx-Designer-Discussions/Convert-String-into-Valid-Date-Format/...
After having successfully made that string-to-date adjustment, I then realized that my REGEX tool now requires some tweaking. The REGEX tool precedes my string-to-date conversion.
Quick summary:
A) SELECT tool outputs string value, e.g., "Oct 13 2009 12:00AM"
B) REGEX tool removes time; therefore, output is now, e.g., "Oct 13 2009" (format = "Mon dd yyy")
Current issue:
- Per A), not all of my records include the "12:00AM" suffix. For instance, some records are stored as, e.g., "10/27/2008" (string).
- Therefore, any records which do NOT have the "12:00AM" suffix are converted into NULL values (see JPG below).
My question:
- How do I properly modify the REGEX tool so that all records will be converted into string "Mon dd yyy" (with or without the "12:00AM" suffix)?
- The latter format appears to be necessary as the REGEX tool is followed by a DATETIME tool which converts input string = "Mon dd yyy" into "YYYY-MM-DD".
Thank you.
Solved! Go to Solution.
Try this in a formula tool. It converts either format into a date, then reformats it to match your requirement.
DateTimeFormat(IIF(Contains([EMP_START_DATE],"/"), DateTimeParse([EMP_START_DATE],"%m/%d/%y"), DateTimeParse([EMP_START_DATE],"%b %d %y")),"%b %d %Y")
@ binuacs -- thank you for your feedback. Your REGEX solution worked great. However, it resulted in a format where my DateTime tool (after the REGEX) no longer worked as envisioned. I'm sure I could tweak it accordingly.
@ Christina_H -- also, thank you for your proposed solution. This one does work perfectly as it converts the string date into the required (Mon dd yyyy) so that the next tool (DateTime) properly converts the string into a date data type. AWESOME!!!
Again, I'd like to thank everyone for your contributions. 'Very much appreciated.
@TomBock If you want to end up with a DateTime field, drop the DateTimeFormat function. The whole formula is converting either date format into a standard date, then reformatting it. If you just use the below it will convert it directly to a date (set the field type in the formula tool to Date or DateTime)
IIF(Contains([EMP_START_DATE],"/"), DateTimeParse([EMP_START_DATE],"%m/%d/%y"), DateTimeParse([EMP_START_DATE],"%b %d %y"))
Ok... that sounds great. That's even better!