Community Spring Cleaning week is here! Join your fellow Maveryx in digging through your old posts and marking comments on them as solved. Learn more here!

Alteryx Designer Desktop Discussions

Find answers, ask questions, and share expertise about Alteryx Designer Desktop and Intelligence Suite.
SOLVED

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

StephenM
6 - Meteoroid

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?

9 REPLIES 9
fmvizcaino
17 - Castor
17 - Castor

 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
Alteryx Alumni (Retired)

Try using the datetime tool with the following configuration: 

 

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

 

echuong1_0-1577128381382.png

estherb47
15 - Aurora
15 - Aurora

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
6 - Meteoroid

Hi Fernando -

This works perfectly.  Thank you for your assistance!

StephenM
6 - Meteoroid

Esther -

Thank you for your assistance.  This worked perfectly!

fmvizcaino
17 - Castor
17 - Castor

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
6 - Meteoroid

Hi Echong1 - 

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

 

Thanks all!

fmvizcaino
17 - Castor
17 - Castor

Thank you @echuong1 ,

 

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

 

estherb47
15 - Aurora
15 - Aurora

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

Labels