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

Hi, 

Agreed!

{
value("12/01/2021", "dd/mm/yyyy") /* This gives 12/1/2021 5:30 AM GMT+05:30 */,
value("13/01/2021", "dd/mm/yyyy") /*This gives Date out of range (13012021 */
}

I think it always considering first value as month. that's why if we give more than 12 in days its says out of range.

if you convert your data to mm/dd/yyyy this format this function will work. Lets wait for other's suggestions

I see similar behavior. Please open a support case and ask whether this is expected.

It also returns "date out of range" for this:

value("2021-02-23", "yyyy-mm-dd")

This was a great excuse to build out a custom expression to parse loosely formatted dates, which I can see as something very handy to have laying around.  If you want to bypass the value() function with it's unexpected results, try out the code sample below.  local!dates has been populated with all examples from this thread and a few other different formats, from those this will return a list of date.

Disclaimer: Not the most readable code I've written :).  This would benefit from a few helper rules, but I wanted to get it all in one expression for a POC.  It can be adjusted for cleanliness, maintenance and additional formatting/delimiter potentials from here.

Note there are some assumptions as well:

 - YEAR value will always be a length of 4

 - MONTH can be either integer under 13, or a short or long value of text (e.g. "Sep" or "September", not "Sept") - can be expanded

 - DAY value is always after month value if both can be either, e.g. "10-1-2021" = Oct 1, not Jan 10

a!localVariables(
  local!dates: {"31/01/2021","2021-01-31","Feb 7 2020","10 apr 2021","10-apr-2021","03 21 2021","06-FEB-2021","August 10, 2021","2021-06-15","10-01-2021","07/9/2021","12/01/2021","13/01/2021","2021-02-03"},
  local!possibleDelimiters: {" ","/","-"},
  local!monthsLong: {"JANUARY","FEBRUARY","MARCH","APRIL","MAY","JUNE","JULY","AUGUST","SEPTEMBER","OCTOBER","NOVEMBER","DECEMBER"},
  local!monthsShort: {"JAN","FEB","MAR","APR","MAY","JUN","JUL","AUG","SEP","OCT","NOV","DEC"},
  local!lists: a!forEach(
    items: local!dates, 
    expression: a!localVariables(
      local!date: fv!item,
      a!forEach(
        items: local!possibleDelimiters, /* attempting split on each possible delimiter */
        expression: split(local!date,fv!item)
      )
    )
  ),
  local!combine: reject(
    rule!APN_isEmpty,
    a!flatten(
      a!forEach(
        items: enumerate(count(local!dates)),
        expression: a!localVariables(
          local!index: fv!index,
          a!forEach(
            items: local!lists[local!index],
            expression: if(
              count(fv!item)=3,
              stripwith(
                joinarray(fv!item,"-"), /* combine back to a common delimiter */
                ", " /* remove commas, spaces or any other chars here */
              ),
              null
              /* bonus prize, write any other formats to the DB for review */
              /*a!localVariables(*/
                /*local!write: fn!executeStoredProcedure(dataSource,procedure,{input: fv!item}),*/
                /*null*/
              /*)*/
            )
          )
        )
      )
    )
  ),
  local!data: a!forEach(
    items: local!combine,
    expression: a!localVariables(
      local!values: split(fv!item,"-"),
      /* gather some metadata here to keep logic cleaner below */
      local!value1: index(local!values,1,null),
      local!value2: index(local!values,2,null),
      local!value3: index(local!values,3,null),
      local!wrk: a!map(
        length1: len(local!value1),
        length2: len(local!value2),
        length3: len(local!value3),
        isInt1: not(rule!APN_isEmpty(tointeger(local!value1))),
        isInt2: not(rule!APN_isEmpty(tointeger(local!value2))),
        isInt3: not(rule!APN_isEmpty(tointeger(local!value3))),
      ),
      local!wrk2: a!map(
        canBeDay1: and(local!wrk.isInt1,local!wrk.length1<3),
        canBeDay2: and(local!wrk.isInt2,local!wrk.length2<3),
        canBeDay3: and(local!wrk.isInt3,local!wrk.length3<3),
        canBeMonth1: or(and(local!wrk.isInt1,local!wrk.length1<3,if(local!wrk.isInt1,local!value1<13,false)),and(not(local!wrk.isInt1),or(contains(local!monthsShort,upper(local!value1)),contains(local!monthsLong,upper(local!value1))))),
        canBeMonth2: or(and(local!wrk.isInt2,local!wrk.length2<3,if(local!wrk.isInt2,local!value2<13,false)),and(not(local!wrk.isInt2),or(contains(local!monthsShort,upper(local!value2)),contains(local!monthsLong,upper(local!value2))))),
        canBeMonth3: or(and(local!wrk.isInt3,local!wrk.length3<3,if(local!wrk.isInt3,local!value3<13,false)),and(not(local!wrk.isInt3),or(contains(local!monthsShort,upper(local!value3)),contains(local!monthsLong,upper(local!value3))))),
        canBeYear1: and(local!wrk.isInt1,local!wrk.length1=4),
        canBeYear2: and(local!wrk.isInt2,local!wrk.length2=4),
        canBeYear3: and(local!wrk.isInt3,local!wrk.length3=4),
      ),
      
      a!map(
        raw: fv!item,
        day: if(and(local!wrk2.canBeDay2,local!wrk2.canBeMonth1),local!value2,if(and(local!wrk2.canBeDay1,local!wrk2.canBeMonth2),local!value1,if(and(local!wrk2.canBeDay3,local!wrk2.canBeMonth2),local!value3,if(and(local!wrk2.canBeDay2,local!wrk2.canBeMonth3),local!value2,null)))),
        month: if(and(local!wrk2.canBeDay2,local!wrk2.canBeMonth1),local!value1,if(and(local!wrk2.canBeDay1,local!wrk2.canBeMonth2),local!value2,if(and(local!wrk2.canBeDay3,local!wrk2.canBeMonth2),local!value2,if(and(local!wrk2.canBeDay2,local!wrk2.canBeMonth3),local!value3,null)))),
        year: if(local!wrk2.canBeYear1,local!value1,if(local!wrk2.canBeYear2,local!value2,if(local!wrk2.canBeYear3,local!value3,null))),
       )
    )
  ),
  
  reject(
    fn!isnull,
    a!forEach(
      items: local!data,
      expression: if(
        or(
          rule!APN_isEmpty(fv!item.day),
          rule!APN_isEmpty(fv!item.month),
          rule!APN_isEmpty(fv!item.year)
        ),
        null,
        date(
          fv!item.year,
          if(
            rule!APN_isEmpty(tointeger(fv!item.month)),
            if(
              len(fv!item.month)=3,
              wherecontains(upper(fv!item.month),local!monthsShort)[1],
              wherecontains(upper(fv!item.month),local!monthsLong)[1]
            ),
            fv!item.month
          ),
          fv!item.day
        )
      )
    )
  )
)