Engine Works
Under the hood of Alteryx: tips, tricks and how-tos.
Date formatting in Alteryx seems to be a hard nut to crack for a lot of users. Most of us might get overwhelmed by all the datetime functions available in Alteryx and not take the time to fully understand them. Then while building workflows we run into datetime issues and either find relief on the Community pages or by trying out multiple functions from the Help pages.
In an effort to help those drowning in datetime waters, some users already gave us some great insights. For example, @HenrietteH wrote the Tool Mastery | DateTime) article, and @RodL wrote a Knowledge Base post about the central role of the DateTimeParse and DateTimeFormat functions in Alteryx (Date-Conversions). Although these insights are great, in my opinion, the thing missing is a concise overview of the datetime functions in Alteryx that makes it accessible for all users to understand that it’s really just LEGO (and come on, who doesn’t like LEGO). Therefore I did my best to make such a concise overview of the datetime functions that Alteryx offers and try to get you going in the world of datetime functions.
Where to start?
Let’s keep things simple (for now), you just want to use one datetime function but you don’t have a clue were to start. Well, at least you took the effort to read this blog and probably looked at the visual (hooray!). First, I will tell you something about the visual in general in the part about structuring the datetime functions. Second, and probably more important, I will tell you something about the basics of datetime functions.
Structuring the DateTime functions
Picture a stereotypical teenager’s room - more often than not it’s a mess. They’re told to clean it up once in a while, putting stuff in boxes or drawers (or under the bed). It not only helps to clean up the mess by following a certain structure, it also helps to structure daily routines or structure information (especially when you’re as easily distracted as I am). Structures give us a certain peace of mind.
Go over to the Alteryx Help site to find the DateTime functions, and you will see an extensive overview of all datetime functions, including the parameters (rules or limits that control what something is or how something should be done), specifiers, and separators. It’s really complete and has everything you need to get going (I do love it).
However, you might find it a bit overwhelming at first. Therefore, I took the time to categorize the datetime functions into seven distinct categories that should lead you to the most suitable functions for you to use:
- convert a datetime to a string
- convert a string to a datetime
- convert a string (in iso) or a number to a datetime
- find the difference between two datetime values
- change the datetime to another date or time
- find a number from a datetime
- generate the current (or near) datetime (don’t have one)
The philosophy behind these categories is that you as a user want to achieve a certain goal and that we can distinguish these functions within these seven “goal” categories. When making these categories I structured the information to get a more sensible overview for the most of us.
In total there are 18 functions, and all of them make use of just seven parameters (yes, just seven). For your convenience, I also placed these parameters in the visual (except for all specifiers, separators and language parameters, you can look them up on the Alteryx Help site for DateTime functions). I was surprised by the fact that there are just seven parameters, that doesn’t sound too bad, does it? This means that when you master these seven parameters (or are able to use the visual in conjunction with the Help site for specifiers and separators), you’re basically capable of making all datetime functions.
Understanding the basics
The DateTime tool really is a great tool, as @RodL already pointed out; it essentially gives a convenient representation of the two greatest datetime functions that Alteryx uses, namely DateTimeFormat (convert a datetime to a string) and DateTimeParse (convert a string to a datetime). Understanding these two functions will empower you although they are also arguably the hardest datetime functions to remember which is which! I bet you will probably end up using these two in 95% of the cases. Master these, you master all datetime functions.
What about these functions then? Well, let’s have a closer look. The first function is the DateTimeFormat(dt, f, l) function. This function is used to convert a datetime field into a string. (See Alteryx Help for more information on data types.) The two options to fill in the dt parameter are: 1) write a datetime value in ISO (yyyy-mm-dd HH:MM:SS ) format like “2021-11-04 11:05:34” (you can leave out the time part if you don’t need it) or 2) use an existing datetime or date field. The important thing here to remember is that the Alteryx engine prefers ISO formats. So when do you most likely use this function? This function has the power to spit out string fields with the whole datetime value, or just the year, the day, the number of the day in the month, the name of the weekday and so on. It gives you the power to get varied format representation of the datetime field.
The format that we want is represented in parameter f. To set up this parameter, you need to have some basic understanding about specifiers and separators. I advise you to go to the help site immediately, DateTime functions help!
The first column on the Help site is Specifiers (seen above), a list of the 31 specifiers that Alteryx has, all of them beginning with %. These denote the string format of the output field. You probably already picked up that you have to start that f parameter with %, great! Each specifier represents a component of the datetime field that you used for parameter dt.
For example, %B extracts the “11” from the date [2021-11-04 11:05:34] and returns the full month name (November). %d gives you the day of the month, in this case 04. It will return 00 if you try to enter %H (a time specifier) on the date field. You can make all kind of exotic combinations of these specifiers. For example, DateTimeFormat(“2021-11-04”, “%B%e%A”) returns “November 4Thursday,” isn’t that exotic! Most of the time however, you will probably keep the same formatting (or just switch the months and years) like “%d%m%Y”.
The more experienced users reading this blog will probably laugh at my formatting: “why is this guy not using any separators??” Separators make even more sense of the formatting in parameter f; they give structure to the format. As the name implies, they separate the specifiers. Separators mostly take the form of / , -, or a space, but you can also get interesting by using @ for example. Although you don’t need to use them, you probably will use them in the majority of your cases. To get back to the ISO format, it will end up like “%d-%m-%Y”, notice that you always have the specifiers starting with % followed by a letter and then a separator. You can also have multiple specifiers after each other followed by multiple separators like “%Y--------", I’m not sure who would use that to be honest).
The final parameter is the optional language parameter l, that I personally never use (that might be because Dutch is not supported). Some of the specifiers represent names of days or months, so you want to present those names in your preferred language. Alteryx made a smart trick for that. This parameter will return day or month names in the language of your preference. To do that you have to fill in the corresponding language code - again, look at the help site, it’s all there (Language Parameters)! For example, DateTimeFormat(“2021-11-04”,”%B-%A”, “es”) returns “noviembre-jueves”, the Spanish representation of “November-Thursday”.
Now that you understand the DateTimeFormat function, it’s time to dive into the DateTimeParse function. Thank goodness I don’t have to write as much for this function as it is a lot of repetition of what you just learned. The DateTimeParse function converts a string to a datetime format. Again, you have two options to fill in the dt parameter: 1) write a datetime value in ISO format (yyyy-mm-dd HH:MM:SS or 2021-11-04), remembering that the time part is not necessary, or 2) take an existing string field (for those paying attention, these are almost identical to the DateTimeFormat options). The important thing to remember is that Alteryx wants to spit out an ISO format date or datetime, so you have to clarify to what kind of format you are feeding it (“the machine”).
After filling the dt parameter you must specify the f parameter as with the DateTimeFormat function. As stated, we use the f parameter to clarify what kind of format we are feeding Alteryx. Read the part about specifiers and separators again to build out the f parameter for the resulting string. The only difference from DateTimeFormat is that DateTimeParse can handle 19 specifiers instead of 31 (just read through the list, Datetime functions help).
One last example: if you have a string field with the format of “20-11-2021” then we need to specify the f parameter as “%d-%m-%Y”, built it out using the right specifiers and separators. So it’s about matching f with the incoming string format so that Alteryx can translate it to the ISO format, that’s all!
You just learned the two most important datetime functions in Alteryx. Now you are ready to fix 95% of your datetime problems (that’s my best guess at least). You’ve learned about these important functions and you have some fundamental understanding of the ISO format that Alteryx loves (yyyy-mm-dd HH:MM:SS). Understanding that format is key to understanding most other functions. Let’s have a look at some other functions.
There are 18 datetime functions and you already mastered two of them. Five other functions don’t have any parameters and are more or less self-explanatory, and all are in the category “generate the current (or near) datetime (don’t have one)”). This leaves us with 11 functions, of which all except two use a dt parameter; the two not using the dt parameter are ToDate and ToDateTime. These two functions convert a string, number or date into a date(time), even an Excel number representation of a date(time). This makes it easy for us to understand that basically all 11 functions either want a 1) field as a date(time) data type, or 2) an entered value between quotes (in ISO), like “2021-11-04 11:05:34”, or 3) another function that results in a date(time) in ISO like DateTimeNow() or 4) a Excel number representation of a date(time).
Knowing the options for the dt parameter helps you to construct most of the 11 functions. For example, six functions within the category “find a number from a datetime” all use only the dt parameter. The DateTimeDay("2021-11-04") function returns 4 as it the 4th day in the month. This leaves us with three functions that need some extra attention: DateTimeDiff, DateTimeAdd and DateTimeTrim.
To use the DateTimeDiff(dt1, dt2, u) function (which subtracts the second argument from the first and returns it as an integer), you have to have feed the function two dt values and select a value from the list given for parameter u (units, see my visual). It even works when you compare a date to a datetime, for example: DateTimeDiff("2021-11-07 11:05:34", "2020-11-04", "hours") returns 8843 as the number of hours between the two entries. Parameter u is also used in DateTimeAdd(dt, i, u), so again you have take one of the values from the list. This function lets you add or subtract an amount of time from a date(time) value. Just make sure you always use a whole integer for the i parameter (interval) like 10 or -10 (no fractions). In this case, DateTimeAdd("2021-11-07 11:05:34", -10, "hours") would result in 2021-11-07 01:05:34 and setting i as 10 would return 21:05:34 (24H notation, that is). One last function left to learn! DateTimeTrim(dt, t), which allows you to remove unwanted portions of the datetime field. I would say that parameter t is at least related to parameter u, it is the list of options on how to trim your date (e.g. lastofmonth, year, day, even hours or minutes). The function is here to give you the opportunity to standardize dates. For example DateTimeTrim("2021-11-07 11:05:34", "firstofmonth") returns “2021-11-01 00:00:00.”
You promised us LEGO
At the beginning of my blog, I told you the DateTime functions are just like LEGO. Now that you have some fundamental understanding of the functions, it’s time to give you some examples of how you can stack those functions and make it even more awesome to use them. The real power of these functions comes into play when you stack them. So, could you give us some examples of when you would stack these functions? Of course!
Most of the time I stack these functions whenever I don’t have a date field and want something dynamic (changing every time I run my workflow). For example, let’s say we always want the date(time) 10 days from today. To have today we would just need DateTimeNow() or DateTimeToday() depending whether you need the time. To add some units of time we will need DateTimeAdd(dt, i, u). As we will add to the current date(time), it will result in the following expression DateTimeAdd(DateTimeNow(), 10, “days”), resulting in “2021-11-15 08:56:56” (the date of writing this piece is 2021-11-05).
Another example of using these functions stacked is when you have to write some weird string values in reports stating a day of the week. The requirement is to have a sentence like “Today it’s [name of day of the week]”. In order to make this sentence out of thin air you end up in your Formula tool writing the following expression "Today it's " + DateTimeFormat(DateTimeNow(),"%A"). It’s a good habit to understand that the sentence is a string and you need the day of the week in string format, so basically you want to convert a datetime to a string (first category).
That’s it, you hopefully have become a true master in the order of datetime (make sure to wear that title with pride). I hope this blog and especially the visual helps (new) users to get a better understanding of the DateTime functions in Alteryx.
Links used:
- Tool Mastery DateTime - https://community.alteryx.com/t5/Alteryx-Designer-Knowledge-Base/Tool-Mastery-DateTime/ta-p/38302
- Date Conversions - https://community.alteryx.com/t5/Alteryx-Designer-Knowledge-Base/Date-Conversions/ta-p/3587
- Help Page DateTime functions - https://help.alteryx.com/current/designer/datetime-functions
- Help Page DateTime tool - https://help.alteryx.com/current/designer/datetime-tool
Banner image by ajale-1481387
Astronomical clock image by mcsc1995-3539629
Clock tower image by Metin Ozer
