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!

Tool Mastery

Explore a diverse compilation of articles that take an in-depth look at Designer tools.
Become a Tool Master

Learn how you can share your expertise with the Community

LEARN MORE

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 DateTime Tool! 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.

Additional Information
Click on the corresponding language link below to access this article in another language -
Portuguese
Spanish
French
German
Japanese

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 

Felo
5 - Atom

Hello All,

 

I am trying to calculate a date back on time. I need to set up first to use the date from an specific column on month back. And once it is calculated (Ex From August to July 2021) then calculate 5, 10, 15 and 20 years.

 

Could some one help me with this? I've been trying for a while but I can't get any positive result,

 

Thank you in advance

 

Felo

mvtejano
7 - Meteor

Interesting and very helpful. Thank you.

Gayitri420
7 - Meteor

thanksxplanation for this 

srilakshmi123
7 - Meteor

hhtps://community.altery.com/t5/alteryx-community-english/ct-p/externa

bhaskardurga
5 - Atom

Thank you so must and helpful in date time

Cheshta22
6 - Meteoroid

I have a mixture of date and time format in GMT timings example if it says 3/10/2000 23:00:00 the actual date would be 4/10/2000. If the data is 3/10/2000 00:00:00 then no changes required.

parveena
5 - Atom

 

<Error>
<Code>AccessDenied</Code>
<Message>Access Denied</Message>
<RequestId>25B496E3A17A0ED5</RequestId>
<HostId>
o7oGfd/HtL5jt/y4MVZGbpqSvLOj2eykTTy1JvBasrXuhSao+PTHy6P/POlKqpNo4I6YKStTh/c=
</HostId>
</Error>
Pavani_2004
5 - Atom

LIKED IT

Muneer2509
5 - Atom

The Date Time Parse link above gives this error: 

<Error>
<Code>Access Denied</Code>
<Message>Access Denied</Message>
<Request Id>25B496E3A17A0ED5</Request Id>
<Host Id>
o7oGfd/HtL5jt/y4MVZGbpqSvLOj2eykTTy1JvBasrXuhSao+PTHy6P/POlKqpNo4I6YKStTh/c=
</Host Id>
</Error>
HenrietteH
Alteryx
Alteryx

@Muneer2509

Which link exactly gives you the error?

 

I just went through all the links in the article and they all work for me but I may have missed one. 

 

Some of them now point to the new Knowledge Base maybe you don't have access to that?
https://knowledge.alteryx.com/index/s/

 

 

asljneibi
5 - Atom

This tool helped me differentiate fridays from the rest of weeks for all year. Thank you

Deepak315
5 - Atom

The Date Time Parse link above gives this error: 

<Error>
<Code>Access Denied</Code>
<Message>Access Denied</Message>
<Request Id>25B496E3A17A0ED5</Request Id>
<Host Id>
o7oGfd/HtL5jt/y4MVZGbpqSvLOj2eykTTy1JvBasrXuhSao+PTHy6P/POlKqpNo4I6YKStTh/c=
</Host Id>
</Error>
0