ALTERYX INSPIRE | Join us this May for for a multi-day virtual analytics + data science experience like no other! Register Now
The Alteryx Community will be temporarily unavailable for a time due to scheduled maintenance on Thursday, April 22nd. Please plan accordingly.

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