Alteryx designer Discussions

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

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

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

Highlighted
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")

Highlighted
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.

 

Highlighted
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?

Highlighted
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

Highlighted
Alteryx Partner

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")

Highlighted
Atom

Perfect solution! Thank you @cmcclellan!!!

Labels