Working with dates in Alteryx Designer

by Nathan Purvis

Background

Working with dates in Alteryx Designer often requires a little bit of extra effort, particularly for those that are used to working with other softwares such as Excel. The main reason for this is that - for the sake of calculations, filtering and storing data - Alteryx can only handle date/datetime fields that follow a specific, ISO format. The International Organization for Standardization (ISO) is a nonprofit, worldwide group that comes together in order to set internationally-accepted standards. In the case of dates, this standard/format is YYYY-MM-DD. For datetime, a HH:MM:SS time element is added to become YYYY-MM-DD HH:MM:SS. Alteryx will store and treat any other format as a string data type.

Implications

Due to this requirement, when building workflows there are extra considerations we need to make if we want to do things like display dates in other formats i.e. 12 September 2023 (vs its ISO equivalent of 2023-09-12). Likewise, when we have ‘dates’ feeding into Alteryx in alternative formats - such as 31 Dec 2020 - we need to convert them into ISO format, either permanently or temporarily, in order to use them in calculations and filtering and such.

Like most things in Alteryx, there is more than one way to manipulate strings into dates and vice versa. We can leverage the DateTime tool, or write expressions using date functions like DateTimeParse() and DateTimeFormat() within the Formula tool. Don’t worry, we’ll cover both methods below!

Converting from ISO to custom formats

As mentioned above, we can take two different approaches when we want to go from an ISO to custom date displays. Custom formats are often used to make timestamps more user-friendly as it is a lot easier to read something like August 12, 2023 than 2023-08-12. To begin with, we’ll cover the DateTime tool approach.

Here, we’re starting with a handful of records that follow ISO standards. A handy way of checking this is to look within the ‘Metadata’ tab on the results pane within Alteryx:

Now, using the DateTime tool we can convert these into a nicer, more readable form. The tool has a bunch of options already pre-built, as well as a ‘Custom’ option where you can define the exact format you want to end up with, using the identifiers found on this help page. A couple of examples of these conversions can be seen here:

Note: The DateTime tool doesn’t have an identifier that allows for 12-hour time displayed with AM/PM. If you want this formatting, you need to actually use DateTime function arguments - which can be found here - like so (%I and %p being the key focus):

On the other hand we have my favourite approach - a formula tool that leverages the DateTimeFormat() function. This leads to the exact same outcome, but as we’re writing an expression here, it requires setting up the equivalent of a ‘custom’ format each time. The identifiers needed for the function are in the link just above and a couple of examples can be seen here:

For both of the methods shown in this blog, we can add whatever we want around the identifiers such as characters, spacing and words, as we are ultimately just creating a custom string:

Converting from custom formats to ISO

In order to do the inverse of the section above, it’s actually very simple. We literally just do the inverse, whereby we tell Alteryx the format of the incoming string fields, and it can use that to convert them for us.

Firstly, the DateTime tool - we just need to ensure that the initial configuration is set to ‘String to Date/Time format’. Then we can set the format of the incoming string field, using the predefined formats or writing out a custom one if needed:

When it comes to the Formula tool approach, we are going to use the DateTimeParse() function here. This works in the exact same way as as the DateTime tool, where we provide the format that our starting string takes and get a genuine ISO datetime as a result:

Once again, the identifiers needed to see how we represent certain date and time elements can be found in the official Alteryx help links provided earlier in this blog.

We hope you have found this article useful and that it can help you get over a bit of the confusion that can sometimes arise when working with dates and times within Alteryx. For more content, please feel free to check out our other blogs & challenges, and be sure to drop us a follow on Twitter to keep up-to-date with new releases!