Convert String into Valid Date Format
- 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
Hello Experts:
This is my first time posting in this forum. I need some assistance with converting a string into a valid date format.
Quick summary:
A) SELECT tool outputs string value, e.g., "Oct 13 2009 12:00AM" >> [EMP_START_DATE]
B) REGEX tool removes time; therefore, output is now, e.g., "Oct 13 2009" >> [EMP_START_DATE_CLEAN]
C) DATETIME tool shall convert value from, e.g., "Oct 13 2009" to, e.g., "10-13-2009" (i.e., mm-dd-yyyy) >> [EMP_START_DATE_CLEAN_2]
At the present time, however, I get the following error:
ConvError: DateTime (63): EMP_START_DATE_CLEAN_2: Cannot convert "Oct 13 2009" to a date/time with format "%m%d%Y" and language "English": Expected a number for Month: 'Oct 13 2009'
My question: How do I properly configure the DATETIME tool as defined in C)? See below current setup and error output.
Thank you.
a) Current Setup:
b) Error Output:
Solved! Go to Solution.
- Labels:
- Error Message
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
@TomBock
First point, you might be missing space in "%m%d%Y"
It probaly should be "%m %d %Y"
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
Those specifiers are for the DateTimeParse function. For the DateTime tool I believe you’ll need to use: Mon dd yyyy
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
Hi @TomBock ,
DateTime tool can convert either
from Date/Time format to String
from String to Date/Time format.
In your case, your input is "Oct 13 2009" and your desired output is "10-13-2009", which are both "String" type.
If you want to convert in that way, you need to first convert from String to DateTime type, and then convert the DateTime data to a new format of String.
If you use Formula tool, the formula would be as follows:
DateTime = DateTimeParse([Input],"%b %d %Y %I:%M%p")
Output = DateTimeFormat([DateTime],"%m-%d-%Y")
And you don't need to use RegEx tool, as Alteryx has specifiers for various date/time format.
https://help.alteryx.com/20223/designer/datetime-functions#specifiers
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
@Qiu -- thank you for the feedback. Unfortunately, no change.
@DataNah -- perfect! Your solution worked brilliantly!
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
Thank you for everyone's contributions. I very much appreciate it.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
@TomBock
Yes, I am sorry it should be "%b %d %Y".
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
Again, the solution provided above works great.
Unfortunately, I realized that my REGEX tool now requires some tweaking. That is, not all records include the "12:00AM" suffix.
I posted a new topic @ REGEX tool requires "tweaking" - Alteryx Community
Tweaking will ensure that all records come into as "Mon dd yyyy" in order for the DateTime tool to properly convert strings into date values.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
Hi @Qiu
Date in input file with out zero like 7-02-2024 is not giving any output while using below formula - Please advice
DateTimeParse([End_Time_2],"%Y-%m-%d")
Attached the file for reference
