DateTime tool issue

[Stanislaw]

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:

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

So I applied the DateTime tool and selected Custom:

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 · Accepted solution]

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.

 

[echuong1]

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.