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

[rituraj963]

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?

[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.

[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

[vizAlter]

Hi @rituraj963 — Did you try my solution?

Or please attached your sample file(s) and workflow if possible.

[ImadZidan · Accepted solution]

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.

[rituraj963]

Hi @vizAlter,

I was not able to open your packaged workflow as it was built in a version higher that what my client allows me t use. I went through your screenshot that you attached and I can infer that the output date is not in mm/dd/yyyy format (it is in the standard ISO format YYYY-MM-DD). I need a date output in mm/dd/yyyy format with date datatype.

Let me know if I got anything wrong.

[vizAlter]

@rituraj963 — Did you try to run a test on the both ~~> Date in YYYY-MM-DD (Date format by Alteryx) and Date in mm/dd/yyyy (String format), and check the generated output?

(Agree with @ImadZidan)

You may like to read: Convert date/time data (in native/ISO format) into a format for use by another application. Note tha... 

And, The native format of the functions is the extended ISO format of: yyyy-mm-dd HH:MM:SS (e.g., 2016-02... 

You should be your mm/dd/yyyy format in the output file: