Maveryx Community Resources

Get up-and-running on the Community and with Alteryx in record time.

DateTime Guide

Alteryx Community Team
Alteryx Community Team

Banner (3).png



Heading 1 - Green.pngGetting Started 


Alteryx uses three data types relating to date and time data: 


Screen Shot 2022-09-02 at 2.32.31 PM.png


There is an interactive lesson on understanding data types, which walks through the differences between the main datatypes in Alteryx and when to use each type. 




Not all databases accept DateTime format (some require date and time in separate columns), so in addition to knowing what Alteryx can do, it’s good to understand what your database system can do. 


If you are new to working with DateTime data in Alteryx, then a good place to start is by understanding the DateTime tool. You can review the help documentation for the tool as well as the tool mastery article. The functionality of the DateTime tool can also be replicated with functions in the Formula tool (see DateTime Functions section). However, it is important to note that the syntax for a date in the DateTime tool is yyyy-mm-dd, while the syntax in the formula tool is %Y-%m-%d.  


Screen Shot 2022-10-14 at 11.51.44 AM.png


If you want to use a date or time field in any formulas, it must be in Alteryx DateTime format (yyyy-mm-dd hh:MM:ss). So, let’s look at how to change the field type. 


Heading 2 - Green.png

Changing a Field Type to DateTime


If your date or time field comes into your workflow as a string value, then there are a few ways you can convert the field to a DateTime field.  

  1. You can use the DateTime tool:  
  2. You can use the Formula tool: 


Heading 3 - Green.png

DateTime Functions 


Check out the DateTime Functions interactive lesson for an overview of DateTime Functions in the formula tool. 


Screen Shot 2022-10-10 at 3.40.30 PM.png


We also have a popular blog on this subject: DateTime Functions Cheat Sheet. Here is the summary “Cheat sheet” from the post: 


CristonS_1-1636477779122 (2).png


Specifiers & Separators 


In addition to the functions used in the Formula tool, you need to know the specifiers and separators to use in the functions. For example, “%b” refers to an abbreviated month name (e.g., “Sept”). You will need to use these specifiers in some functions, like when you use the DateTimeFormat() function. 


Screen Shot 2022-10-14 at 1.07.49 PM.png

You can also use separators to build up your date format. 


Screen Shot 2022-10-14 at 11.25.49 AM.png


For a comprehensive list of all DateTime functions, specifiers, and separators, you can refer to this help documentation 


Creating calculated DateTime fields 


There are many discussion threads where you can see these DateTime functions in action. Review these solutions before posting your DateTime question because it may have already been answered! 


If you read these discussions, you will notice that there are often two steps in DateTime problems: first, converting the field from string to DateTime, and then building a formula with DateTime functions to get to the solution. 


And here’s a bonus—a knowledge base article on How To Find The Last Day Of Any Month In Alteryx. 


Extracting data from a DateTime field 


Once you have your DateTime field in Alteryx, you might want to extract certain information from that field, like the day of the week, the quarter, or the year. Here are some discussion threads on how to extract other fields of data from your DateTime field: 


DateTime formatting 


After you have worked with the field in Alteryx, you might want to output it in a format different from “yyyy-mm-dd" for reporting, downstream processing, or storage. Check out the thread DateTime Format to see how you can use the custom format option to output the exact string format you want from the DateTime tool.  


Screen Shot 2022-10-12 at 12.11.19 PM.png

Here are some additional formatting examples: 


Heading 4 - Green.png

Troubleshooting Common Errors 


There are many discussion threads where Alteryx users run into trouble with converting to and from DateTime format. Take a look at the solutions to these threads to understand the thought process and methodology for converting these fields: 


Do you have a discussion forum thread, blog, or other content on Community that has helped you when working with DateTime fields? Drop it in the comments below. 


8 - Asteroid
8 - Asteroid

I have a question around null values and comparing dates. If you need to compare two date fields (open and close dates) and you want to make sure the opening date is before the close date. If there is no close date it is null. Do you have any advice when comparing dates with null values? It is especially difficult with the multirow formula tool.

Alteryx Community Team
Alteryx Community Team

Hi @Brinker,


What is your objective when you want to compare dates and one is null?


Since you cannot do any calculations with a null date, you could filter out rows with nulls in open and close and then handle those separately. Then you could calculate the date based on other fields, i.e. if the average time between open and close is 15 days, and you have just an open date, then you fill the null close date with DateTimeAdd([opendate], 15, "days"). If it cannot be calculated from other fields, then best to leave it null and/or leave it out of your analysis if it's a small amount of rows.




8 - Asteroid

Hi @MeganDibble 


Thank you for the advice for removing the null dates. I had to do some summarizing, filtering and joining, but I got to my answer.




5 - Atom

thank you for the advice