Community Spring Cleaning week is here! Join your fellow Maveryx in digging through your old posts and marking comments on them as solved. Learn more here!

Maveryx Community Resources

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

DateTime Guide

MeganDibble
Alteryx Community Team
Alteryx Community Team
Created

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. 

 

MeganDibble_3-1665768948946.png

 

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. 

 

Comments
anayet1988
8 - Asteroid
Brinker
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.

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

 

Thanks,

Megan

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

 

Best,

Carl

Zaim0719
5 - Atom

thank you for the advice