REST Query - Filter on Date Property

Epicor ERP 10
,
1

I’m attempting to execute a REST query using the filter parameter on a date property.

Using the v1 API, I can get it to work by the following filter: ClockInDate eq datetime’2019-12-13’

This same filter string does not work in the v2 API. Here is a list of what I’ve tried, and the result:

Filter: ClockInDate eq datetime’2019-12-13’
Result: Unrecognized ‘Edm.String’ literal ‘datetime’2019-12-13’’ at ‘15’ in ‘PayrollDate eq datetime’2019-12-13’'.

Filter: ClockInDate eq ‘2019-12-13’
Result: A binary operator with incompatible types was detected. Found operand types ‘Edm.DateTimeOffset’ and ‘Edm.String’ for operator kind ‘Equal’.

Filter: ‘ClockInDate’ eq ‘2019-12-13’
Result: No error, but result set is empty and there should be multiple records.

Filter: ClockInDate eq 2019-12-13
Result: Sorry! Something went wrong. Please contact your system administrator.
AKA Internal Server Error 500. I checked the app server event log and found this: Operand type clash: date is incompatible with int.

I found a thread with a similar issue here but it seems to be specific to BAQ queries, and I’m guessing it’s for the v1 API. This issue is specific to v2.

2

From the second test, it looks like ClockInDate is of type Edm.DateTimeOffset. The following might help (TBH- i didnt fully read it)

Edit

From another site:
“…try to pass on DateTimeOffset using a JSON format in the query string, like 2014-05-06T22:24:55Z, that should work.”

For example
http://localhost:1234/api/values/1?date=2017-04-17T05:45:18.070Z

Try adding the time portion, like T00:00:00Z

3

Thanks for the suggestion. I tried using this format a few different ways, but no success.

Filter: ClockInDate eq 2019-12-13T00:00:00Z
Result: Sorry! Something went wrong. Please contact your system administrator.
Internal Server Error 500. App server event log reads: Incorrect syntax near ‘T00:’.

Filter: ClockInDate eq ‘2019-12-13T00:00:00.000Z’
Result: A binary operator with incompatible types was detected. Found operand types ‘Edm.DateTimeOffset’ and ‘Edm.String’ for operator kind ‘Equal’.

Filter: ClockInDate eq datetimeoffset‘2019-12-13T00:00:00.000Z’
Result: Unrecognized ‘Edm.String’ literal ‘datetimeoffset’2019-12-13T00:00:00.000Z’’ at ‘15’ in ‘ClockInDate eq datetimeoffset’2019-12-13T00:00:00.000Z’’.

4

Sorry, it is a bug. We are working on the fix of handling datetime and guid literals in REST API v.2.
In general, the fix is ready in the 10.2.600 and 10.2.500 development branches.
Because of the New Year holidays the fix in 10.2.500 will be released in January.

Anyway, the following format - http://localhost:1234/api/values/1?date=2017-04-17T05:45:18.070Z is correct.

3 Likes
5

Thank you for the update Sergey. I am currently using 10.2.500, so I look forward to the patch in January.

6

I have this same problem in 10.2.400.7. Is this issue fixed with a patch in this version or is my only option to update to the latest?

7

Sorry, I’m not working on REST anymore. AFAIR, we didn’t patch 400 at that time. Also, as far as I know, the company does not release patches for 10.2.400 anymore (10.2.500 is the oldest supporting version now). So, update to the newer version is the only option. But you can reach the support.

8

Has there been any resolution to this what I am trying is not working.

9

It works for me like this now $filter=ClosedDate eq 2008-01-08T00:00:00Z

1 Like
10

Sure enough that does work with my BAQ the key is including the Time. I was trying the datetime’…’ and datetimeoffset’…’ which weren’t working at all.

1 Like