We are aware of an issue with the Search bar. Please use Advanced search for the time being while we troubleshoot. Thanks for your patience as we work on improving the community!

Alteryx Designer Knowledge Base

Definitive answers from Designer experts.

Tool Mastery | DateTime

HenrietteH
Alteryx
Alteryx
Created
DateTime.png

This article is part of the Tool Mastery Series, a compilation of Knowledge Base contributions to introduce diverse working examples for Designer Tools. Here we’ll delve into uses of the DateTime Toolon our way to mastering the Alteryx Designer:

 

Date/Time data can appear in your data in string formats (text fields) or date formats.The DateTime Tool standardizes and formats suchdata so that it can be used in expressions and functions from the Formula or Filter Tools (e.g. calculating the number of days that have elapsed since a start date). It can also be used to convert dates in datetime format to strings to use for reporting purposes.

 

The functionality of the DateTime tool is very similar to the DateTime Functions in the Formula Tool, but a little more user-friendly.

 

To change a date datatype to a formatted string field, all you do is select the date field to be formatted, what you would like the formatted date to look like and the name of the new field Alteryx will create:

 

2019-03-01_9-53-56.png

 

 

Converting a date contained in a text field to a proper date format works similarly: you select the field to be formatted, the string that matches the current format and what the output field should be called.

 

2019-03-01_10-03-49.png

 

A date stored as date format will always appear as yyyy-mm-dd when viewed in a Browse Tool .

 

The dy., Month dd, yyyy and day, dd Month, yyyy options allow you to output the day of the week. This is especially useful if you want to differentiate between weekends and weekdays, e.g. if you want to remove business days from your data (if you only have the start and end dates and want to remove business days, refer to this article).

 

If the DateTime tool doesn't cover your needs, go over to this article and this post to explore the options using Date/Time functions in the Formula Tool.

 

By now, you should have expert-level proficiency with the DateTimeTool!If you can think of a use case we left out, feel free to use the comments section below! Consider yourself a Tool Master already? Let us know at community@alteryx.com if you’d like your creative tool uses to be featured in the Tool Mastery Series.

Comments
Schmid-E
7 - Meteor

The DateTimeParse link above gives this error: 

<Error>
<Code>AccessDenied</Code>
<Message>Access Denied</Message>
<RequestId>25B496E3A17A0ED5</RequestId>
<HostId>
o7oGfd/HtL5jt/y4MVZGbpqSvLOj2eykTTy1JvBasrXuhSao+PTHy6P/POlKqpNo4I6YKStTh/c=
</HostId>
</Error>
Schmid-E
7 - Meteor

So does the DateTimeFormat link: 

<Error>
<Code>AccessDenied</Code>
<Message>Access Denied</Message>
<RequestId>25B496E3A17A0ED5</RequestId>
<HostId>
o7oGfd/HtL5jt/y4MVZGbpqSvLOj2eykTTy1JvBasrXuhSao+PTHy6P/POlKqpNo4I6YKStTh/c=
</HostId>
</Error>
HenrietteH
Alteryx
Alteryx

@Schmid-E

The links have been fixed, thank you for bringing this to our attention! 

Schmid-E
7 - Meteor

You're welcome!

 

MJ
8 - Asteroid

More precision (milliseconds and perhaps also nanoseconds) hopefully coming later this year:  https://community.alteryx.com/t5/Alteryx-Designer-Ideas/Add-DateTime-type-with-milliseconds/idi-p/20...

Gina2021
8 - Asteroid

Is it possible to clear the Input Data conversion error of ______ "is not a valid DateTime"? When I place the DateTime toll directly after the Input tool and run, it works perfectly, but I'm still left with that input error.  Is this normal?  Thanks so much!

HenrietteH
Alteryx
Alteryx

Hi @Gina2021 

That error is telling you that you have an invalid date in your data that it can't convert. 

It will create a null value for the value that it can't convert and process the rest of the data. If you don't mind that, you can just ignore the conversion error.

Gina2021
8 - Asteroid

Ooohhh! That explains why I have so many null dates! So are users finding ways to fix the date format upstream? I'm pulling in an Access DB. Thanks!

HenrietteH
Alteryx
Alteryx

Hi @Gina2021 

 

It's hard to say without looking at the data. It could be inconsistent formatting? E.g. some dates are formatted as mm/dd/yyyy and others as yyyy-mm-dd

 

You could use the data profiling tool to take a look at the dates to see what they look like. 

 

You can also use the formula tool to too format dates. This article gives some good examples: https://community.alteryx.com/t5/Alteryx-Designer-Knowledge-Base/When-Time-is-of-the-Essence-Using-D...

 

 

Gina2021
8 - Asteroid

Amazing @HenrietteH ! Thanks so much!

skomaragiri
6 - Meteoroid

I'm trying to convert this Ship Date quantity to be a Date, however I am running into errors. Not sure what is wrong?

 

skomaragiri_0-1620964263561.png

 

Gina2021
8 - Asteroid

@skomaragiri It looks like your date is coming in as a string with the quotes around it.  

https://community.alteryx.com/t5/Alteryx-Designer-Discussions/Convert-String-to-Date/td-p/335719