This site uses different types of cookies, including analytics and functional cookies (its own and from other sites). To change your cookie settings or find out more, click here. If you continue browsing our website, you accept these cookies.
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')
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,
Go to Solution.
Go to Solution.
@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")
Hi Rita, I had some success using the following format.
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.
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?
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?
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")
Perfect solution! Thank you @cmcclellan!!!