Convert String into Valid Date Format

[TomBock]

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:

[Qiu]

@TomBock 
First point, you might be missing space in "%m%d%Y"

It probaly should be "%m %d %Y"

[DataNath · Accepted solution]

Those specifiers are for the DateTimeParse function. For the DateTime tool I believe you’ll need to use: Mon dd yyyy

https://help.alteryx.com/20223/designer/datetime-tool

[Yoshiro_Fujimori]

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

[TomBock]

@Qiu -- thank you for the feedback.   Unfortunately, no change.

@DataNah -- perfect!  Your solution worked brilliantly!  

[TomBock]

Thank you for everyone's contributions.   I very much appreciate it.

[Qiu]

@TomBock 
Yes, I am sorry it should be "%b %d %Y".

[TomBock]

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.