Alteryx Community Resources
Get up-and-running on the Community and with Alteryx in record time.DateTime Guide
- Getting Started
- Changing a Field Type to DateTime
- DateTime Functions
- Specifiers & Separators
- Creating calculated DateTime fields
- Extracting data from a DateTime field
- DateTime formatting
- Troubleshooting Common Errors
Getting Started
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.
- You can use the DateTime tool:
- Here is an example for converting “MM/dd/yyyy” to a Date: DateTime Transformation
- You can use the Formula tool:
- See this example on how to convert “09222018” to a Date: Convert String to Date
- See this example on how to convert “07JUN2019:12:56:13.000” to a DateTime: Convert string to datetime
- See this example on how to convert “20220306143422” to a DateTime: Convert String to date
DateTime Functions
Check out the DateTime Functions interactive lesson for an overview of DateTime Functions in the formula tool.
We also have a popular blog on this subject: DateTime Functions Cheat Sheet. Here is the summary “Cheat sheet” from the post:
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.
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!
- Calculating the difference between two dates: DateTimeDiff function, Number of days between two dates
- Calculating a date relative to today: Date Time today
- Adding & subtracting values from dates: Add or Subtract X Number of Days
- Creating a Date field which is the starting week Sunday of a date: Date Conversion
- Generating a field with all dates between the start and end date: Generate dates between [StartDate] and [EndDate]
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:
- How to extract month and year from a date?
- Month Only From Date
- Date conversion to Day of Week
- Convert month name to number
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.
Here are some additional formatting examples:
- Changing the Date format to YYYYMMDD: Date Formatting
- Changing the DateTime format to DD-MM-YYYY HH:SS: Change Date Format in Alteryx
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:
- DateTime tool conversion error
- DateTime tool error
- DateTime tool issue
- DateTime Parse/ DateTimeFormat returns Not a Valid DateTime
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.
