web
You’re offline. This is a read only version of the page.
close
Skip to main content
Community site session details

Community site session details

Session Id :
Dynamics 365 Community / Forums / Microsoft Dynamics AX (Archived) / DateTime OData filter ...
Microsoft Dynamics AX (Archived)
Answered

DateTime OData filter does not work

(0) ShareShare
ReportReport
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

I have the same question (0)
  • 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.

  • 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

    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.

  • 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

    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!

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

Responsible AI policies

As AI tools become more common, we’re introducing a Responsible AI Use…

Pallavi Phade – Community Spotlight

We are honored to recognize Pallavi Phade as our Community Spotlight honoree for…

Congratulations to the October Top 10 Community Leaders!

These are the community rock stars!

Leaderboard > 🔒一 Microsoft Dynamics AX (Archived)

#1
Martin Tocauer Profile Picture

Martin Tocauer 4

#2
Alexey Lekanov Profile Picture

Alexey Lekanov 3

#3
Community Member Profile Picture

Community Member 2

Last 30 days Overall leaderboard

Featured topics

Product updates

Dynamics 365 release plans