Alteryx Designer Desktop Discussions

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

Help Converting Date (String Data) into Actual Date (i.e., MM/DD/YYYY)

Nameless_One
6 - Meteoroid

Hi All,

 

Can someone please help me with creating a formula to convert string data for a date field into a usable date field in Alteryx? 

 

I already tried using the "DateTime" tool, but unfortunately it won't work with the way the raw data is coming out of my company's Oracle system.

 

I'm now trying to convert a string format of: "DD-JAN-20X1 00.00.00." see below.  This one's pretty trickly. I tried playing around with the formula tool (and using the "DateTimeParse" function, but I kept running into roadblocks).

 

Any help is greatly appreciated! Thanks so much.

 

Nameless_One_0-1666733606852.png

 

Nameless_One_1-1666733692858.png

 

 

7 REPLIES 7
binuacs
21 - Polaris

@Nameless_One One way of doing this with the DateTimeParse function

 

binuacs_0-1666734476446.png

to convert in to MM/DD/YYYY format use the DateTimeFormat function

 

DateTimeFormat(DateTimeParse([Date],'%d-%b-%Y %H.%M.%S'),'%m/%d/%Y')

 

binuacs_1-1666734609945.png

 

SPetrie
13 - Pulsar

If you want to use the Date Time tool instead of the formula @binuacs suggested, you should be able to use a setup like this as well.

 

SPetrie_1-1666734635863.png

 

ShankerV
17 - Castor

Hi @Nameless_One 

 

As you are no longer worried about time, you can use the DateTime tool in the below format to overcome your issue.

 

Also make sure that, you have used dd-Mon-yyyy not as dd-Month-yyyy which might be the root cause of your issue.

 

dd-Mon-yyyy -> Works when your input is 01-Apr-2006 00.00.00

dd-Month-yyyy -> Works when your input is 01-April-2006 00.00.00

 

Hope this helps!!!

 

Solution.jpg

 

Nameless_One
6 - Meteoroid

Unfortunately, this isn't working. I'm getting the below error:

Nameless_One_0-1666739524528.png

 

Nameless_One_1-1666739538107.png

 

gautiergodard
13 - Pulsar

@Nameless_One If you are just looking for the date, please see attached! 

 

You can use formula below:

 

datetimeparse(left([Date],11),'%d-%b-%Y')
gautiergodard
13 - Pulsar

Hello @Nameless_One 

If this post provided you with the answer to your question, please accept it as a solution so that other in the community may reference it in the future.

 

Thank you!

Nameless_One
6 - Meteoroid

Thank you! The DateTime tool solution seemed the most practical solution that worked when I exported the date output. 

 

Labels
Top Solution Authors