DateTime OData filter does not work

(0) Share

Report

Posted on by AB2014

Hi.

if you try to use a DateTime filter, this results in "An error has occurred. The type 'System.DateTimeOffset' for the query operator is not Queryable!"

E.g. /data/Customers?$filter=CreatedDateTime%20gt%202017-01-01T01:01:01Z

No operators seem to work (eq, gt, ne).

I tried to search DevDocs for this, but didn't find any verfication if this is a (missing) feature or bug.

*This post is locked for comments

All responses (5)

Answers (1)

AB2014 on at

Like (0)

Report

RE: D365FO: DateTime OData filter does not work

Hi,

we'll this is a big bummer. The system datetime fields are usually the fields that most interest users who use date entities. Thanks for the info!
Verified answer

Mohd saddaf khan 20 on at

Like (0)

Report

RE: D365FO: DateTime OData filter does not work

Ok. So you have created a extension of CustCustomerEntity and added CreatedDateTime field and named it as let's say ABCCreatedDateTime.

The major problem here is, Data entity extension does not allows regeneration/updation of staging table(as it will cause overlayering). So, to overcome this issue and make field available for Data import/export, its better to create an extension of staging table and add same field here.

However, it does not resolves your issue of filter on createddatetime field. Though, Other UTCDatetime/date fields can be used (via extension/standard) for filter without any issues.

However there are alternate ways for this task

1. Its not recommended, but you can use overlayering instead of extension for CustCustomerEntity then add ABCCreatedDateTime field here. And filter will work like anything.

2. Use a different approach and create a data entity which will be a exact replica of CustCustomerEntity in your working model. Then add ABCCreatedDateTime field from custtable datasource.
AB2014 on at

Like (0)

Report

RE: D365FO: DateTime OData filter does not work

This is an extension field into the Customers entity.

If I'll query a standard data entity with date field that's not UTCDateTimebased, but extends e.g. TransDate, the gt/lt operators work just fine.
Mohd saddaf khan 20 on at

Like (0)

Report

RE: D365FO: DateTime OData filter does not work

I am not sure whats going wrong with your entity, it should work after renaming as mentioned by sukrut. I've not checked it for Createddatetime field, but filter works perfectly for other utcdatetime fields

I've written a blog for Read operations, see if it helps you

dynamicsaxforall.blogspot.in/.../crud-with-odata-and-data-entities-in.html
AB2014 on at

Like (0)

Report

RE: D365FO: DateTime OData filter does not work

My example was a bit bad; we have a BP rule that every object has a custom prefix, so actually I did a custom named field, i.e. ABCCreatedDateTime. But that didn't work.