REST converting ISO dates in character fields to US dates

[josecgomez]

If I were a betting man I’d wager they are using regex because the json payload isn’t explicitly typed and instead of looking up each field to see if they are dates they are matching on regex

[Mark_Wonsil]

@HLalumiere

Take that, REST and JSON apologists!

REST can send XML schema validated data. REST doesn’t care about the payload. This is a JSON processing issue. As others have pointed out, it is determining type based on how what the data “looks” like.

Excel has the same problem. The solution that Excel uses is to prepend a ’ to the data for force the value to a string. If you want to do something similar, you could add the ’ and test for it in a Data Directive and then remove it.

[pimzand (Pim Zandbergen)]

That’s not a solution, that is a workaround that already has been mentioned.
The solution is that Epicor would look at the schema in the deserialization process.
There is no excuse for the current behaviour.

[Mark_Wonsil]

@pimzand

That’s not a solution, that is a workaround that already has been mentioned.
…
There is no excuse for the current behaviour.

Agreed. It is a workaround. I know millions of people put up with that very same issue in Excel. Every. Single. Day.

@pimzand

The solution is that Epicor would look at the schema in the deserialization process.

Yes, Epicor should use the schema to determine types. The question is where does this happen? In every business object? How big of a fix would this be?

How long are we willing to wait for Epicor to come up with a better solution?

[pimzand (Pim Zandbergen)]

Excel does not have a schema for the data it is reading. It has to guess.

Epicor has a schema. For every service, every method.
You can examine the schema in the REST help, it is available in JSON and YAML formats.
It clearly shows where the method is expecting a string, where a date or datetime.

My guess there is a single piece of code that does the deserialization for every REST call.

[Mark_Wonsil]

@pimzand

Excel does not have a schema for the data it is reading. It has to guess.

Again. I agree. And trust me, as one who dabbles in AppSec, user input should never be trusted and every input should be validated against EXPECTED values and not just simple schema type checking. I would like value checks and authorization checks too.

@pimzand

there is a single piece of code that does the deserialization for every REST call.

This is the best-case scenario. How long do you think it would take Epicor to make the change, regression test it, push it to a preview group, then cloud group, and finally to us on-prem users?

[pimzand (Pim Zandbergen)]

Quite a while.
But it is never going to happen if we don’t report it, or consider it acceptable behaviour.

[klincecum (Kevin Lincecum)]

@pimzand

But it is never going to happen if we don’t report it, or consider it acceptable behaviour.

I can promise you, @Mark_Wonsil is not suggesting anything of the sort.

Us natural problem solvers will usually try to give you a workaround, AND want it fixed.

[Mark_Wonsil]

@pimzand

if we don’t report it, or consider it acceptable behaviour.

It HAS been reported though,right? If not, I would include a link to this thread. We can also ping @pferrington and/or @olga if they haven’t already seen this thread. I’m sure that @timshuwy has been lurking here too.

[josecgomez]

After doing some testing I can confirm that this bug is relegated to RPC only. OData method work fine and return and keep the data the way it is.

So a trivial work-around for UDXX record is to just use oData endpoint.

Further testing yields this is not a regression of 2023.X it is present as far back as 10.2.700 (only on the RPC calls)

[klincecum (Kevin Lincecum)]

@josecgomez

it is present as far back as 10.2.700 (only on the RPC calls)

[pimzand (Pim Zandbergen)]

Yes I also checked in 10.2.700 and 2023.1.

Using OData would indeed be the best solution.
Did you check V2 RPC?

[josecgomez]

Yup V2 RPC same issue

[klincecum (Kevin Lincecum)]

@pimzand

Did you check V2 RPC?

I did as well, same.

[pimzand (Pim Zandbergen)]

@Mark_Wonsil

It HAS been reported though,right?

I have reported it at Epicor UK support. But they are hesitant to pass it to Dev.
I guess it could speed up the process if any of you can report it to Epicor US.

[klincecum (Kevin Lincecum)]

@pimzand

I guess it could speed up the process if any of you can report it to Epicor US.

Consider it done.

[klincecum (Kevin Lincecum)]

@klincecum

CS0003791602 - Bug Report - REST/Kinetic UI/Classic - Adding ISO 8601 formatted date string to any STRING(Character) type field, transforming data to US Date String- Data Mangling

Description

If you take an ISO 8601 Date string, “2023-07-17T15:43:42.461Z”, and add it BY ITSELF, to any string type field, either through rest or the client, it will not remain as a string, but will be parsed and mangled to another data format, and stored as the mangled date string.

We have a large discussion on the issue here:

REST converting ISO dates in character fields to US dates

Versions back until at least ->10.2.700 and forward until 2023.1.8

For example, adding a record to UD01, via the custom methods in REST:

https://SERVER/ENVIRONMENT/apps/resthelp/#/select-service/v1/methods/Ice.UD01Svc/Update.BO

    "ds": {
        "UD01": [{
            "Company": "YourCompany",
            "Key1": "AUniqueKey",
            "Character01": "2023-07-17T15:43:42.461Z",
            "RowMod": "A"
        }]
    }
}

The Character01 key, which is a string field, will receive “2023-07-17T15:43:42.461Z”, but store “07/17/2023 15:43:42”

This only occurs when the ISO 8601 date field is the only text in the field, ie:

“2023-07-17T15:43:42.461Z” = “07/17/2023 15:43:42”

“A2023-07-17T15:43:42.461Z” = “A2023-07-17T15:43:42.461Z”

“2023-07-17T15:43:42.461ZA” = “2023-07-17T15:43:42.461ZA”

Which leads us to believe, that somewhere, someone is matching on regular expressions, instead of examining the datatypes upstream.

This is not isolated to UD Tables, but the rest interface as a whole, as you can also reproduce this using any string field in the Kinetic, or Classic UI:

Later on in the discussion thread, Jose Gomez said that the issue does NOT occur in the ODATA methods, but I have not verified myself.

Thank you for looking.

Done

[pimzand (Pim Zandbergen)]

I was under the impression that it does not happen when entering data in classic screens.
Of course, classic screens would show modified data, if that was entered in Kinetic UI.

[klincecum (Kevin Lincecum)]

@pimzand

I was under the impression that it does not happen when entering data in classic screens.

I was under the impression it was, but I just verified it does not, at least not where I am testing.
I will update my bug report accordingly.

[klincecum (Kevin Lincecum)]

@pimzand

I have reported it at Epicor UK support.

Do you have that Case Number, so I can add it?