How to Manipulate Dates in Alteryx

Following these steps, Alteryx will output a new column with the date in ISO format, “2023-06-01”. Below we’ve included a couple of example images to better demonstrate this process.

When converting a date to a string, you will select the format of the string.  With “2023-06-01” as an input, you could select the option of “Month, dd, yyyy” and the tool would output the date as “June 01, 2023”.

9 Quick Alteryx Tips to Optimize Your Data Workflows

If none of the options in the predetermined list meets your conversion needs, you also have the option to specify a custom format using Alteryx’s extensive list of datetime functions.

Formulas to the rescue

When working with any data type, the ability to clean, update and add values to a current dataset is a must; dates are no expectation to this rule. That’s why Alteryx has an entire section of formulas just dedicated to helping you organize and update date values. The formula tool can be found under the “Preparation” tab at the top of the user interface. To use the formula tool, you simply drag it onto the canvas and connect it to a data stream. Once we’ve got our input connected and our formula tool selected, you’ll be greeted by the following menu on the left:

To see all of the different formulas you can choose from, you’ll just want to click on the blue “fx” button on the left-hand side. Alteryx provides many different types of formulas broken down into subcategories, but for this article, we’ll be focusing our attention on the formulas housed under the “DateTime” section:

You can choose whichever formula helps you accomplish your cleansing or analysis goals, and hovering your mouse over any of them will generate a small popup that provides a brief description of what that formula does. We’ve listed a refined selection of some of our frequently used formulas below with a brief explanation of what they do:

• • DateTimeAdd(dt,i,u) – (dt = the column containing your date data or a static date; i = an integer representing how many days/months/years, etc. you want to add to the dates; u = the unit or specifier representing days/weeks/months, etc. as a string value).  For example, DateTimeAdd(‘2023-06-01’,1,’months’) would produce the result ‘2023-07-01’.  More info on this can be found here on Alteryx’s site. We’ve also posted an example of this below:

• • DateTimeNow() – This function requires no parameters and allows you to pull the current date. This can be useful if you want to dynamically add the current date every time you run a workflow or for implementing logic to always pull the most current data from a database using the dynamic input tool.

• • DateTimeDiff(dt1,dt2,u) – (dt1 = the first date you want to use as a comparison; dt2 = the second date you want to compare against the first date; u = the unit of difference you want to have returned (days/weeks/months)). This value is returned as an integer that can be used in later calculations. For example – DateTimeDiff(‘2023-06-01’,’2023-05-01’,”months”) = 1

Taking it a step further

Now that we’ve covered some of the essentials when it comes to working with date data types in Alteryx, let’s discuss some applications that these tools can have when we’re developing. In the previous section we touched on the DateTimeNow() formula function and how we can use it to pull the current date whenever we run a workflow; to take this a step further, let’s say we have a workflow that combines historical data with newly published data and we want to do some processing on the new data before we append the two sets together.

We can use the DateTimeFirstOfMonth(), which performs the exact same function as DateTimeNow() but pulls the first of the current month as opposed to the current day, function in a filter. This will siphon off our new data into a separate run that has its own set of tools and formulas applied to it before it is added back on to our original dataset. This allows us to update our data in a dynamic way that requires no intervention from us as the developer, allowing for automation. 

Another use case we come across often is when querying a database for recent data. The DateTimeNow() function can help us automate a data pull with the Dynamic Input Tool. We can use the value we get from this formula to update a SQL query WHERE clause to pull the date that we receive as an output from this formula, allowing us to automatically run a workflow that only pulls current relevant data with the click of a button.

Conclusion

In this blog we’ve covered the basics of the Date data type, some useful formulas that Alteryx provides to help us manipulate those dates, and use cases for taking those formulas to the next step. We hope this high level overview of dates will give you a good headstart and comfort level when diving into working with dates! 

Best of Luck!
Ariana & Nick