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

Date Conversions - How to calculate time differences with format 09-APR-18 09.30.00.00 PM

Rita01
5 - Atom

Hi, I could use your support.

 

I am trying to calculate the difference between 2 dates in my table. I've tried this:

 

Start_Time = datetimeparse([Original_START_TIME],'%d-%Mon-%Y %h.%m.%s.%ms %X')

End_Time = datetimeparse([Original_END_TIME],'%d-%Mon-%Y %h.%m.%s.%ms %X')

DateTimeDiff([Start_Time],[End_Time],'Second')

 

But it's not working, giving me the error: "expected a number for minute".

 

Examples of dates I have in the file:

 

12-APR-18 03.30.00.000000000 PM

10-APR-18 10.15.00.000000000 AM

 

Thank you in advance for your support,

Rita

6 REPLIES 6
neilgallen
12 - Quasar

@Rita01 to my knowledge Alteryx does not yet support milliseconds as a datetime format. That will be part of your issue. You may need to parse that out or ignore the component of the timestamp.

if it was ignored and your data was "12-APR-18 03.30.00 PM" for example, then the format of the formula to create the datetime would be:

 

datetimeparse([Field],"%d-%B-%Y %I.%M.%S %p")

HasanHb
Alteryx
Alteryx

Hi Rita, I had some success using the following format.  

 

datetimeparse([StartDateValue],'%d-%b-%Y %H.%M.%S')

 

Is MS important to you?

 

 

I noticed you had %mon for month where you actually need %b.  Also, you have to use %I (upper case eye) for hour if you want to use %P (for AM,PM indicator)  

 

Here is the link that I used to figure out all the date format specifiers. https://help.alteryx.com/2018.4/Reference/Functions.htm

 

I hope this helps get you going.

 

Rita01
5 - Atom

Thank you so much for the reply @neilgallen. It still gives me an error: expected AM/PM indication. Is there a way to drop the milliseconds?

Rita01
5 - Atom

Thank you @HasanHb. MS is not important but AM/PM is.

datetimeparse([StartDateValue],'%d-%b-%Y %H.%M.%S') works but is not considering the AM/PM.

How can I ignore the MS and read AM/PM?

 

Thank you!

Rita

cmcclellan
13 - Pulsar

I would use this method :

 

2019-01-17 10_19_26-Alteryx Designer x64 - New Workflow4_.png

 

To remove milliseconds from Start_Time:

Start_Time = left( [Start_Time], 15) + right([Start_Time],3)

 

To convert to DateTime

Output = datetimeparse([Start_Time],"%d-%B-%Y %I.%M %p")

Rita01
5 - Atom

Perfect solution! Thank you @cmcclellan!!!

Labels