Alteryx Designer Desktop Discussions

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

Problems parsing time with ParseDateTime

blyons
11 - Bolide

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!

 

8 REPLIES 8
JohnJPS
15 - Aurora

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
11 - Bolide

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
15 - Aurora

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
11 - Bolide

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
15 - Aurora

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

blyons
11 - Bolide

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
Alteryx Alumni (Retired)

@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
11 - Bolide

Thanks, Kevin. Looking forward to 11.0. Smiley Happy

Labels