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

[Rita01]

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

[neilgallen]

@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]

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]

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]

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 · Accepted solution]

I would use this method :

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]

Perfect solution! Thank you @cmcclellan!!!