Covert String/Number/date (mmddyyyy) to date format (mm/dd/yyyy)
- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Mute
- Printer Friendly Page
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
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?
Solved! Go to Solution.
- Labels:
- Date Time
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
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?
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
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.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
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
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
Hi @rituraj963 — Did you try my solution?
Or please attached your sample file(s) and workflow if possible.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
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.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
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.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
@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...
You should be your mm/dd/yyyy format in the output file:
