• State Suggested Answer
  • +1 person also asked this people also asked this
  • Replies 13 replies
  • Answers 1 answer
  • Subscribers 15 subscribers
  • Views 20992 views
  • Users 0 members are here
Related Discussions
Home » Discussions » Data and Records

Casting dates to and from JSON

jonathanb0001

Hi,

So when Appian writes dates to Json, it cannot cast those values correctly if it reads them back.

load(
  local!data:{
    today()
  },
  local!jsonData: a!toJson(
    local!data
  ),
  local!fromJson: cast(
    typeof(today()),
    a!fromJson(
      local!jsonData
    )
  ),  
  a!sectionLayout(
    contents: {
      a!textField(
        value: local!jsonData
      ),
      a!textField(
        value: local!fromJson
      )
    }
  )
)

Do I have to manually alter the string in order to reorder the date from "YYYY-MM-DDZ" to "DD-MM-YYYYZ" before I run from Json?

Is there a reason that Appian designed it this way?

  Discussion posts and replies are publicly visible

  • 0
    I've just double checked and it looks like it works pretty easily if you cast the date / datetime to string before doing the initial JSON conversion, and the resulting value coming back in from json can be cast back to date / datetime using "todatetime()", though perhaps some of the inherent extra data (seconds, time zone, etc) is lost this way.

    I also am curious what the official recommended methodology is for retrieving the datetime value when the date data type itself is used in the JSON string. It seems like there should be some way to do it automatically without needing to, say, build a manual parser helper rule.
  • 0 in reply to Mike Schmitt

    Huh interestingly it even accepts the format of mmm/dd/yyyy.
    But if given mm/dd/yyyy it will interpret it incorrectly, except when the day is greater than 12 as it seems to do some logical shuffling when it sees an invalid month.
    Pretty strange and it would be great if we could get some explanation on how Appian expects to be given its Json Data, as I think I would prefer it to fail consistently than behave unexpectedly.

  • 0
    Hi ,

    We do actually have the similar request of read and write the date format into the Appian DB, we do generally get the value as Text and convert them it todate . Ideally there is not straight way to do this AFAIK but there was some plugin available in App Market regarding Json Date.
  • 0
    Hello,

    I have to say that passing the dates as integer is easier than handling it as strings.

    tointeger(today())
    The drawback is that you have to cast it back to date( but that is something you are already doing as string in the code you shared)

    Hope this helps

    Jose
  • 0 in reply to josep

    Perhaps, but from what I can find the recommended way to pass dates to JSON is through the ISO 8601 standard. 

    Appian does seem to agree somewhat with this given thats how it casts dates into a JSON format, so why does it expect them to be passed in differently.

    Additionally what if this data isn't originating from the same Appian location but from an external source, I am not familiar with how Appian encodes dates to integers, is this an industry standard, so that I can ensure that the data is converted to integer in a way that Appian can reverse correctly.

    Also this fails for datetimes, so it is kind of a hack.

    a!textField(
    label: todatetime(tointeger(now())),
    value: todatetime(tointeger(today()))
)

    I think that when casting to integer Appian rounds the datetime value up to the closest date value and then converts that to integer.

  • 0 in reply to jonathanb0001
    Well your concern is good, let me see if I can comment anything else on the date as string.

    For your new requirement for datetime you can use it as decimal, from which the decimal part is the time. If you cast it to integer you are truncating the decimals consequently the time.

    Regarding your concern of what happens when you move between environments or different installations. Well the decimal/integer representation uses the date 1/1/2035 00:00 GMT (Appian epoch) as the value 0. That’s why the values are negative when you cast today to integer/decimal. So you can move the values freely between Appian installation.

    Jose
  • 0 in reply to josep
    For reference, when passing date values through JSON we utilize datetext() for input and todate() for output.

    Input:

    a!toJson(
    {
    dateFrom: if(rule!APN_isEmpty(local!dateFrom),"",datetext(local!dateFrom,"MM/dd/yyyy")),
    dateTo: if(rule!APN_isEmpty(local!dateTo),"",datetext(local!dateTo,"MM/dd/yyyy"))
    }
    )

    Output:

    local!search: a!fromJson(ri!input),
    todate(local!search.dateFrom)
  • 0 in reply to Chris
    What if I wanted to pass a CDT into JSON?
    Would you recommend I create a parser rule to break it into a dictionary that JSON can translate properly?
  • 0
    Certified Lead Developer

    I would suggest adhere to the XML standard for date time as while generating the JSON from Appian, Appian would do so as well. Then  while converting it back from JSON you need to convert it to xml format and then cast to datetime.

    ToJson :

    a!toJson(
  value: {
    datestamp: now()
  }
)


    FromJSON :
    load(
  local!jsonData: a!fromJson(
    "{""datestamp"":""2018-11-13T04:54:46.029Z""}"
  ).datestamp,
  if(
    regexmatch(
      "^([0-9]+\d[-][0-9]\d[-][0-9]\d[T][0-9]\d[:][0-9]\d[:][0-9]\d[\.][0-9]+[Z]+)$",
      local!jsonData
    ),
    torecord(
      toxml(
        local!jsonData
      ),
      now()
    ),
    null
  )
)


    you may need the regex if you can't rely on consumer sending the proper data as per xml standards  defined and agreed before hands. Might have to tweak the regex as well.
     

  • 0 in reply to manisht209

    Huh thats really interesting, however I'm having a couple of problems.

    Firstly, I think that regex function is a plugin rather than Appian basic so it seems questionable that it would be Appians recommendation. Although they might reccomend the same just with the out of the box text functions.

    Secondly my Appian installation code suggestor thingy does not suggest that I use torecord() ever and if I manually enter it, I'm not given any details in that little box that usually contains a description and parameter. Given that I can see that Appian has documentation for this in 18.3 I assume this is a problem with my installation. (It does function it just acts like the function is meant to be hidden).

    And finally, if we cannot handle CDTs and have to craft a custom parser rule why not just use datetext? I assume that it would be less expensive.

© 2024 Appian. All rights reserved.
We want to hear from you! Submit a review on Gartner or G2.