ALTERYX INSPIRE | Join us this May for for a multi-day virtual analytics + data science experience like no other! Register Now
1 Day Left! - The Alteryx Community will be temporarily unavailable for a few hours due to implementation of the new SSO experience starting tomorrow at 5pm MDT. Please plan accordingly. For more information, read the blog.

Alteryx Designer Discussions

Find answers, ask questions, and share expertise about Alteryx Designer.
SOLVED

DateTime tool issue

Stanislaw
7 - Meteor

Hello everybody.

 

I'd like to know how convert string text to a specific date format (not the default format).

Also, can I do the modifications in the original column? I wouldn't want to create an extra one.

 

Please let me show you my example (attachment).

I have a table:

 

Stanislaw_0-1612771439042.png

I need to convert Sep. 2020 to 2020.09.01 (yyyy.MM.dd).

So I applied the DateTime tool and selected Custom:

 

Stanislaw_1-1612771535350.png

 

The thing is Alteryx creates an extra column and that's not what I need. The other issue is that the new format is yyyy-MM-dd, and I need yyyy.MM.dd

How to deal with this? Please help! 🙂

 

JosephSerpis
15 - Aurora
15 - Aurora

Hi @Stanislaw like most things in Alteryx their are multiple ways to solve a challenge I mocked up a workflow that produces the output you describe using a formula tool and the Datetime parse and Datetime Format functions. I also needed to increase the size of the original field so the data didn't get cut off. This link showcases the different datetime functions and the meaning of the specifiers I used.

 

Date_08022020.JPG 

echuong1
Alteryx
Alteryx

The Alteryx-recognized date format is YYYY-MM-DD. After you convert your values to dates to have the Month converted to a numeric version and have the date added, you can simply use a replace statement to replace the "-" with a ".". 

 

Alternatively, you can use a formula to do the date conversion and the replace statement all in one.

 

You will need to create a new column with either of these, due to the size of your Date column. It is a string value limited to only 9 characters, but YYYY.MM.DD is 10 characters. You'll either need to create a new column, or use a Select to change the datatype. 

 

Keep in mind that having your data formatted this way makes the value a string, or text. It is no longer a date and date functions (like datetimeadd, datetimediff, etc.) won't be applicable. I only recommend doing this at the very end of your analysis, for presentation purposes as needed.

 

echuong1_0-1612845350270.png

 

Labels