Covert String/Number/date (mmddyyyy) to date format (mm/dd/yyyy)

I see there are multiple posts requesting to covert string / number / date data types to a specific date format. Everywhere I noticed the end result was STRING not a DATE data type. After looking multiple posts, I got frustrated. Does Alteryx have this capability?

I have a string in "mmddyyyy" format. I want the output in "mm/dd/yyyy" format and the data type as DATE. I tried multiple options. DateTime Parse tool can't do it. DateTimeFormat function can't do it. Am I missing anything?

Date Time

Accepted answers

ImadZidan

Hello @rituraj963 ,

I agree with you and despite understanding why Alteryx would want to unify its date format, it is always good to have the option. It is a wide and very frequent discussion.

My suggestion is perhaps have a date column to hold the date format and another column storing your required format in a string value.

When outputting to a file for distribution, output the desired one.

All comments

T_Willins

Hi @rituraj963,

Alteryx holds dates in a specific format (yyyy-mm-dd) for the date field type. If you need to manipulate dates in a workflow, convert your string to a date using either a formula or the DateTime tool, then convert the date back to your desired format as a string. Why does the output need to be a data type of date instead of string?

vizAlter

Hi @rituraj963 — You can try the "DateTime" tool and refer to the solution attached.

Otherwise it's better to provide us your file, we can check that.

Convert Date - String to Date.yxzp

rituraj963

Well there could be various reasons the downstream team is looking for data
type as date. There are like 11 such date fields in odd forms. Few examples
could be like
1. date fields (with date data type) can be used as relative date on Tableau
2. if you are doing performance tuning of a SQL query where you need date
in your filter, preference is date data type.

Think why great people back in time would have created date data type. I
wonder why Alteryx can't solve this simple problem when all database /
other ETL tools can easily manipulate the format of data field and retain
the data type as date.

Regards,
Rituraj Kumar

Quick Links

This months top contributors

atcodedog05 19458

Qiu 15866

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703