Skip to main content

Notifications

Microsoft Dynamics AX (Archived)

DateTime OData filter does not work

Posted on by

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

  • AB2014 Profile Picture
    AB2014 on at
    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 Profile Picture
    Mohd saddaf khan 20 on at
    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 Profile Picture
    AB2014 on at
    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 Profile Picture
    Mohd saddaf khan 20 on at
    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 Profile Picture
    AB2014 on at
    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.

Under review

Thank you for your reply! To ensure a great experience for everyone, your content is awaiting approval by our Community Managers. Please check back later.

Helpful resources

Quick Links

December Spotlight Star - Muhammad Affan

Congratulations to a top community star!

Community AMA December 12th

Join us as we continue to demystify the Dynamics 365 Contact Center

New! Quick response templatesâš¡

Save time with the new custom templates!

Leaderboard

#1
André Arnaud de Calavon Profile Picture

André Arnaud de Cal... 291,188 Super User 2024 Season 2

#2
Martin Dráb Profile Picture

Martin Dráb 230,030 Most Valuable Professional

#3
nmaenpaa Profile Picture

nmaenpaa 101,156

Leaderboard

Featured topics

Product updates

Dynamics 365 release plans