Alteryx uses three data types relating to date and time data:
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.
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.
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.
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.
You can also use separators to build up your date format.
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.
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:
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.
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: