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

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.

Workflow

Date Time

Accepted answers

ShankerV

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

All comments

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')

DAte_1025.yxmd

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

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')

dateparse.yxmd

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.

Quick Links

This months top contributors

atcodedog05 19598

Qiu 15880

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703