[ardoJSON] JSON2RecordList - Strings being converted to date/time

[ardoJSON] JSON2RecordList - Strings being converted to date/time

  
Forge Component
(21)
Published on 30 Aug by Ricardo Silva
21 votes
Published on 30 Aug by Ricardo Silva

I am using the JSON2RecordList action of the latest version of the ardoJSON extension (1.9.3) and I am running into some strange behavior with the extension converting a string to a formatted date/time.

Here is the order of events:

  1. Get raw JSON response from API ('created_on' attribute is in ISO format as a string)
  2. Call JSONSelect on the attribute (an object) that contains the 'created_on' attribute ('created_on' attribute is still in ISO format as a string)
  3. Call JSON2RecordList passing the output of JSONSelect with the ToObject(MyRecordList) input. The list is populated with the one record, since it was an object, and values are assigned. At THIS point, the 'created_on' attribute is assigned with a date/time format like so: "MM/DD/YYYY HH:MM:SS".

The conversion from ISO format to the format described is undesired and I'm not sure why it occurs.

The data type for the attribute on the record list that it is assigning to via JSON2RecordList is set as "Text" and NOT a "Date/Time", yet this method performs a format conversion.

I tried to get around this by then calling the system "TextToDateTime" function on this but the output from this drops the date part of the value and just returns "1900-01-01 HH:MM:SS".

JSON Select:

After JSON2RecordList:


As you can see, my structure is set as "Text":

Thank you for your help!

Solution

Hi Brian,

What happens is that in the raw JSON you get, there's an ISO formatted date time ("2015-12-08T03:34:25Z") so internally it's converted to a date/time. Then it sees your destination field, which is of type string, so it does something akin to a .ToString(), and without format, it gets the default locale's format, which is American, judging the output.

I'm not sure why you have a Text in the structure. If what you receive is a date, make it an attribute of Date type. Then no convertion to text is performed, and you can apply your own formatting if so desired.

Solution

I think Kilian is right. 

I've already noticed in other scenarios that JSON.NET does some magic when it thinks a field is a datetime and might convert it internally, originating these WTF moments.

Were you able to workaround the issue by converting the field to a Date Time in the platform? Do you still need me to look into this?

Thanks for the replies.

I've been able to work around this. Basically I just do a conversion from the date/time to an ISO formatted timestamp after it comes out of the JSON2RecordList action and passed to my logic.

Thanks for the explanation!