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

Date Time

Accepted answers

cmcclellan

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

All comments

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

Quick Links

This months top contributors

FláviaB 262

caltang 177

DanM 167

IraWatt 157

gawa 157