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-format-problem/m-p/32051/highlight/true#M5883
http://community.alteryx.com/t5/Data-Preparation-Blending/Converting-String-to-DateTime/m-p/846/highlight/true#M12
Thank you in advance for your help!
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
@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
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.
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.
