OData string error and problem with date formats

0
votes

I try to get data to excel with OData. When I use link below it works fine: http://xts/odata/AuditAggregationOdata or http://xts/odata/AuditAggregationOdata/?$filter=(ObjectId%20eq%2041143%20)

But when I try to use some date range or funcion with date it does not work. I try to write everything but without right result:

1. http://xts/odata/AuditAggregationOdata/?$filter=year(FirstRead)%20eq%202016

Error message: Unable to connect We encountered an error while trying to connect Details: "Microsoft.Mashup.Engine1.Library.Resources.HttpResource: Request failed: OData Version: 3 and 4, Error: The remote server returned an error: (500) Internal Server Error. (Exception has been thrown by the target of an invocation.) OData Version: 4, Error: The remote server returned an error: (500) Internal Server Error. (Internal Server Error) OData Version: 3, Error: The remote server returned an error: (500) Internal Server Error. (Exception has been thrown by the target of an invocation.)"

Or this one

2. http://xts/odata/AuditAggregationOdata/?$filter=(FirstRead%20gt%20datetime%272019-01-01T00:00:00Z%27%20and%20FirstRead%20lt%20datetime%272019-01-03T00:00:00Z%27)

Error message: Details: "Microsoft.Mashup.Engine1.Library.Resources.HttpResource: Request failed: OData Version: 3 and 4, Error: The remote server returned an error: (400) Bad Request. (The query specified in the URI is not valid. A binary operator with incompatible types was detected. Found operand types 'Edm.DateTimeOffset' and 'Edm.DateTime' for operator kind 'GreaterThan'.) OData Version: 4, Error: The remote server returned an error: (500) Internal Server Error. (Internal Server Error) OData Version: 3, Error: The remote server returned an error: (400) Bad Request. (The query specified in the URI is not valid. A binary operator with incompatible types was detected. Found operand types 'Edm.DateTimeOffset' and 'Edm.DateTime' for operator kind 'GreaterThan'.)"

Note: Property Name="FirstRead" Type="Edm.DateTimeOffset"/

Thank you

1
filterconnectionodata

1 Answers

0
votes

It seems to be an internal server error.

When I tried different entity but with same conditions, it worked fine. I put the right solution below:

http://xts/odata/ProfileData(path='Odata_test%7Codata_test')?$filter=year(From)%20eq%202019

http://xts/odata/ProfileData(path='Odata_test%7Codata_test')?$filter=From%20ge%20datetimeoffset%272016-01-01%27%20and%20From%20lt%20datetimeoffset%272020-01-01%27