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

[Nameless_One]

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.

[binu_acs]

@Nameless_One One way of doing this with the DateTimeParse function

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

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

[SPetrie]

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

[ShankerV · Accepted solution]

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!!!

 

[Nameless_One]

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

[gautiergodard]

@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]

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]

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