Converting string (dd-MON-yyyy hh:mm PM) to date

[StephenM]

Hello,

I have a string field that I'm trying to convert to a date.  An example of one of the strings is 13-DEC-2019 04:09 PM.  I am trying to convert the string to a date/time field, but I cannot seem to get the time to work correctly.  If is use the DateTime tool, it will convert all times to AM.  I have also tried to use DateTimeParse, but I cannot get the AM/PM portion to work correctly there either.

Any suggestions?

[fmvizcaino · Accepted solution]

 Hi @StephenM ,

You can use the DateTimeParse function using the formula tool as example attached.

DateTimeParse([Field1],'%d-%b-%Y %I:%M %p ')

You can find all specifiers at https://help.alteryx.com/2019.4/Reference/Functions.htm

Let me know if that works for you.

Best,

Fernando Vizcaino

[echuong1 · Accepted solution]

Try using the datetime tool with the following configuration: 

%d-%b-%Y %I:%M %p

[estherb47 · Accepted solution]

Hi @StephenM !

Have you tried %p as the qualifier for AM/PM? I believe that the AM/PM needs to be capital letters in your data for it to work.
In a formula tool, this should work:
DateTimeParse([Date],"%d-%b-%Y %I:%M %p")

The standard date time is a 24 hour clock.

Let me know if that helps. 

Cheers!

Esther

[StephenM]

Hi Fernando -

This works perfectly.  Thank you for your assistance!

[StephenM]

Esther -

Thank you for your assistance.  This worked perfectly!

[fmvizcaino · Accepted solution]

Glad to help @StephenM 

If you think we achieve your expectation, please mark the answer as correct to help other Alteryx users finding this solution! 

Thank you and happy holidays! 🙂

Fernando Vizcaino

[StephenM]

Hi Echong1 - 

I was able to successfully use the datetime tool with your assistance.

Thanks all!

[fmvizcaino]

Thank you @echuong1 ,

I had no idea we could use the parameters in the datetime tool the same as the datetimeparse function.

[estherb47]

yes @fmvizcaino , I love that feature of the Date Time tool as well! For me, it's often easier than a formula tool.

Cheers!

Esther