Parsing Dates in Alteryx

Correctly formatting dates for processing and analysis is a familiar occurrence to any data analyst. Luckily, Alteryx offers a number of ways to quickly do this.

DateTime Parse Tool

The DateTime Tool, found in the parse tab of the toolbar is one of the quickest ways to translate between string and date types. Simply decide if you're converting string to date or date to string, then select the field you're converting, name your output field, and provide info about you're formatting.

The DateTime tool uses d (day) M (month) y (year) and h (hour), m (minute), s (second) in order to translate between our date formatting and Alteryx's date type which formats as yyyy-MM-dd.

Alteryx even allows you to specify the language of the in or output data.

If none of Alteryx's provided date formats aligns with your formatting then you can use the Custom input in order to specify the correct formatting.

When inputting custom formatting, Alteryx's documentation provides a useful translation dictionary here, including multiple languages in addition to English.

Key Date Formats:

• d: Day of the month as digits, without leading zeros for single-digit days.
• day: The full name of the day of the week.
• dd: Day in 2 digits, with leading zeros for single-digit days. On input, leading zeros are optional.
• dy: Day of the week as a 3-letter abbreviation. On input, full names are accepted but Alteryx doesn't check that the day of the week agrees with the rest of the date.
• EEEE: The full name of the day of the week.
• M: A single-digit month, without a leading zero.
• MM: Month as digits, with leading zeros for single-digit months. On input, leading zeros are optional.
• MMM: The abbreviated name of the month.
• MMMM: The name of the month spelled out.
• Mon: A 3-letter abbreviation of the name of the month. On input, full names are also accepted.
• Month: Name of the Month. On input, abbreviations are also accepted.
• yy: Year represented only by the last two digits. When converting from a string, two-digit years are mapped into the range from the current year, minus 66 years to the current year, plus 33 years. For example, in 2016, a two-digit year will be mapped into the range: 1950 to 2049. On input, four digits are also be accepted.
• yyyy: Year represented by the full 4 digits. On input, 2 digits will also be accepted and mapped as done for the “yy” pattern.

Date Parsing with the Formula Tool

Perhaps the DateTime tool isn't for you, or you'd like an even more complex output. Luckily, Alteryx also allows you to convert dates in the Formula tool using the DateTimeParse() function. If you want to convert date to string you have to use the DateTimeFormat() function.

Simply call upon DATETIMEPARSE() in the Formula Tool's widow and input the parameters.

DateTimeParse(string, format, [language])

Language is optional an you ignore it for the English default and opt to just put the string column name and then the format of that incoming string. The main downside is that DateTimeParse formatting is different (and less intuitive) than DateTime Tool's formatting.

Ex: If you have a string formatted as MM/DD/YYYY:  DateTimeParse([DateString], "%m/%d/%Y")

Alteryx provides a helpful documentation table for these conversion on their help page here.

If you're looking to convert from date to string you can use the same formatting but with the DateTimeFormat() function.

DateTimeFormat(date, format, [language])

For additional information and a helpful pro/con comparison between these approaches I recommend exploring Le's Blog on the topic or attending a Learn What the Data School Learns session since I will be teaching a hands on workshop on Date and Text Parsing this March!

Date Time Format in Alteryx - The Data School

There are many data types that data analysts usually work with. String, number and date are mostly used in preparing the data

The Data School powered by The Information Lab