Format parameter of value() function does not appear to be working as documented

Hi all, I'm trying to clean up some very messy data by converting text strings representing dates into date objects. The input strings are extracted from various documents (using Appian Document Extraction), so the messiness of the inputs is not something I have control over and cannot be standardized ahead of time.

I've found multiple posts on this forum featuring recent recommendations from Appian employee   to use the value() function for this purpose, so it does seem like this is an expected use case for the function; I'm not trying to do something crazy here.

However, the value function does not appear to be working as documented...? This is copied directly from the latest version (21.2) of the official value() documentation on the value() function:

Syntax

value ( text, [format] )

text: (Text) The string of characters to be converted into a number or date.

format: (Text) The input format of the value, such as "mm/dd/yyyy".

When I try to test this functionality using the Test Input box lower on the same page (so it's clearly not an issue with my version of Appian or anything; this is directly on the Appian Documentation site linked above), I get errors and weird behavior. Some examples:

  • value("31/01/2021","dd/mm/yyyy") returns Date out of range (31012021) instead of the expected date 1/31/2021
  • value("2021-01-31", "yyyy-mm-dd") returns Date out of range (-20210131) instead of the expected date 1/31/2021
  • value("Feb 7 2020", "mmm d yyyy") returns 72020 (an integer) instead of the expected date 2/7/2020
  • value("10-apr-2021") returns 4/10/2021 (correct, without even needing the format!), but both value("10 apr 2021") and value("10 apr 2021", "dd mmm yyyy") (with and without specifying the format) return 102021 (an integer) instead of the expected date 4/10/2021

What am I doing wrong in the above cases? Is there a list of date formats the format parameter accepts somewhere that I can reference? I've searched high and low but haven't been able to locate one on the Appian Documentation site.

Thanks!

  Discussion posts and replies are publicly visible

Parents Reply Children
No Data