DateTime Guide

Banner (3).png

Getting 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.

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

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!

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:

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:

Changing the Date format to YYYYMMDD: Date Formatting
Changing the DateTime format to DD-MM-YYYY HH:SS: Change Date Format in Alteryx

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.

Designer

Accepted answers

All comments

anayet1988

https://help.alteryx.com/10.1/Reference/DateTimeFunctions.htm this is also a really useful link

Brinker

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.

MeganBowers

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.

Thanks,

Megan

Brinker

Hi @MeganBowers

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.

Best,

Carl

Zaim0719

thank you for the advice

Quick Links

This months top contributors

atcodedog05 19458

Qiu 15866

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703