OData : Operands of logical operator 'AND' are not valid

Question

I am trying to call an OData report with the following URL :

https://myXXXXX.sapbydesign.com/sap/byd/odata/crm_customerinvoicing_analytics.svc/RPCRMCIVIB_Q0001QueryResults?&$filter=PARA_CAL_DAY%20gt%20datetime%272017-01-01T00:00:00.000%27%20and%20PARA_CAL_DAY%20lt%20datetime%272017-01-02T00:00:00.000%27&$skip=0

As you can see I am trying to use the and operator inside a filter property but I received the error in the title of this post.

I tried :

$filter=(MyFilter1) and (MyFilter2)
$filter=(MyFilter1 and MyFilter2)
$filter=(MyFilter1 AND MyFilter2)

But I still have an error. Any ideas ?

Answer 1

All the evidence (documentation, forums, Github page, etc.) led to believe this type of filtering doesn't work. Out of desperation I tried a suggestion from this closed issue on the Github dev guide, even though the answer to that question specifically said that it wouldn't work. And, guess what? It did! If you ge and le instead of gt and lt the and operator in the filter works. For example, this doesn't work:

CreationDateTime gt datetimeoffset'2023-05-20T00:00:00Z' and (CreationDateTime lt datetimeoffset'2023-05-20T01:00:00Z')

But this works:

CreationDateTime ge datetimeoffset'2023-05-20T00:00:00Z' and (CreationDateTime le datetimeoffset'2023-05-20T01:00:00Z')

Answer 2

Just in case someone stumbles upon this thread first, this is error occurs when the same field is used to filter the dataset twice (greater than+less than, greater than+less than or equal to, etc) SAP issue report