Problems parsing time with ParseDateTime

[blyons]

I am having difficulties with the ParseDateTime function that I don't understand. I am referring to the documentation at https://help.alteryx.com/10.6/index.htm#Reference/DateTimeFunctions.htm#Format, and the results are not what I expect. I have attached a workflow that illustrates the problem, along with a version that works, but seems suboptimal. Any help would be appreciated. Is this a problem with my understanding of the documentation? Is the documentation wrong? Is this a bug?

I found a couple of threads that are related to this, and one of them contains the formula that works which I included here. Related threads:

http://community.alteryx.com/t5/Data-Preparation-Blending/Date-Time-Conversion-Filter-Issue-AM-PM-fo...

http://community.alteryx.com/t5/Data-Preparation-Blending/Converting-String-to-DateTime/m-p/846/high...

Thank you in advance for your help!

[JohnJPS]

It very well may be a bug... not sure, but it doesn't seem like DateTimeParse with %I is working...

The following should work in a regular formula without too much fuss:

IF EndsWith([Test Datetime],"PM") THEN
    DateTimeAdd(DateTimeParse(Replace([Test Datetime],"PM",""),"%m/%d/%Y %H:%M:%S"),12,"hours")
ELSE
    DateTimeParse(Replace([Test Datetime],"AM",""),"%m/%d/%Y %H:%M:%S")
ENDIF

Basically, it uses %H which appears to work fine, and then adds 12 hours if the input had PM.

[blyons]

Thank you. That is a little cleaner than my previous work-around.

And thank you for confirming that %I is not working for you as well. Can you confirm that %X is also not working for you?

I'm wondering if these are bugs, or if the documentation for %I (and consequently %p and %P because they depend on %I) and %X should be changed to "not supported."

I have attached an updated version show both those that work and those that don't.

[JohnJPS]

I notice in the documentation (example: https://help.alteryx.com/9.5/Reference/DateTimeFunctions.htm), it mentions that %I is "output only" wihch I'm guessing means you can use it for formatting but not parsing.  Since %I wasn't useful for parsing, I didn't really bother with %X.

[blyons]

Interesting that the 9.5 document is different than 10.6. The 9.5 doc says %I (capital "eye") is output only, but the 10.6 doc has specifications for its use for input, even though it doesn't work. The 9.5 doc doesn't have any comment at all for %X - it doesn't say "output only," but it doesn't specify it's use for input either, while the 10.6 doc does specify it's use as input.

To me, this implies that they intended to change the functionality, but that functionality was either never built or has bugs. I consider this a bug, either way.

Thank you for your help. I'm marking your answer as correct because it is the best workaround for this bug.

[JohnJPS]

(I guess I should bookmark the current docs rather than just click on what Google hands me... :-)

[blyons · Accepted solution]

In implementing this, I realized this formula breaks with times during the noon and midnight hours, in which cases it returns a datetime 12 hours in the future.

For reference for others looking to solve the same problem, the formula that works is:

IF EndsWith([Test Datetime],"PM") and Substring([Test Datetime],11,2) != "12" THEN
    DateTimeAdd(DateTimeParse(Replace([Test Datetime],"PM",""),"%m/%d/%Y %H:%M:%S"),12,"hours")
ELSEIF EndsWith([Test Datetime],"AM") and Substring([Test Datetime],11,2) == "12" THEN
    DateTimeAdd(DateTimeParse(Replace([Test Datetime],"PM",""),"%m/%d/%Y %H:%M:%S"),-12,"hours")

ELSE
    DateTimeParse(Replace([Test Datetime],"AM",""),"%m/%d/%Y %H:%M:%S")
ENDIF

---

[KevinP · Accepted solution]

@blyons

Thank you for bringing this to our attention and working with me regarding this issue. As per our discussion I have spoken with our development and documentation teams regarding the issue. We were able to confirm that this is expected behaviour in 10.6 and that the documentation was incorrect. Specifically, development confirmed that the 10.6.8 release uses the same datetime library as prior versions and as such functionality should be identical to what is documented for 10.5. We are in the process of updating the help documentation to correctly reflect the expected behaviour. 

As an additional note development also confirmed that the datetime library responsible for these functions is being updated with release 11.0. Current builds of the 11.0 release already have the updated library, and the functionality of these tools is being expanded as reflected in the documentation currently. This means that %I (Capitol i) will work with the DateTimeParse() function moving forward along with any other descrepancies. 

Thanks

[blyons]

Thanks, Kevin. Looking forward to 11.0.