cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Mark_Holtham
Regular Visitor

dynamics 365 URL filter by date

Hello all,

 

I've been told by an engineer to move to the Dynamics 365 9.0 api for Power BI queries but I'm finding that I receive an error when filtering by date.  I suspect it is a formatting issue because I can filter by text field but not by date.

 

I've tried this with various date formats but getting the error below:

 

OData.Feed("https://<tenant>.api.crm.dynamics.com/api/data/v9.0/invoices?$filter=createdon eq datetime'2018-01-01'")

 

Error

OData Version: 3 and 4, Error: The remote server returned an error: (400) Bad Request. (Unrecognized 'Edm.String' literal 'datetime'2018-01-01'' at '13' in 'createdon gt datetime'2018-01-01''.)

 

Any chance someone can point me in the right direction?

 

In case it helps, the original query was: https://<tenant>.api.crm.dynamics.com/XRMServices/2011/Organization.svc/InvoiceSet?$filter=CreatedOn gt datetime '2018-01-01'

 

Mark

1 ACCEPTED SOLUTION
v-yuezhe-msft
Microsoft
Microsoft

@Mark_Holtham,

Change your code to the following:

OData.Feed("https://<tenant>.api.crm.dynamics.com/api/data/v9.0/invoices?$filter=createdon eq 2018-01-01
")

1.PNG


Reference:
https://msdn.microsoft.com/en-us/library/mt628816.aspx

Regards,
Lydia

Community Support Team _ Lydia Zhang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

2 REPLIES 2
v-yuezhe-msft
Microsoft
Microsoft

@Mark_Holtham,

Change your code to the following:

OData.Feed("https://<tenant>.api.crm.dynamics.com/api/data/v9.0/invoices?$filter=createdon eq 2018-01-01
")

1.PNG


Reference:
https://msdn.microsoft.com/en-us/library/mt628816.aspx

Regards,
Lydia

Community Support Team _ Lydia Zhang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

Thank you, that worked. Thats the one variation I didnt try!

 

One version of this issue I still have though relates to cross table filtering by date.  For example I have the similar date filtering requirement for saleorderdetails where I want to filter only the ones where the salesorder has a specific date.

 

Normally I would expand and then filter but this does not seem to be supported anymore.

 

Source = OData.Feed("<tenant>.api.crm.dynamics.com/api/data/v9.0/salesorderdetails?&$expand=salesorderid&$filter=salesorderid/createdon gt 2016-11-01

 

Any chance you can point me in the right direction for this one as well?

 

Thanks

Helpful resources

Announcements
PBI User Groups

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group

March Update

Check it Out!

Click here to read more about the March 2021 Updates!

secondImage

The largest Power BI virtual conference

100+ sessions, 100+ speakers, Product managers, MVPs, and experts. All about Power BI. Attend online or watch the recordings.

secondImage

Experience what’s next for Power BI

See the latest Power BI innovations, updates, and demos from the Microsoft Business Applications Launch Event.

Top Solution Authors
Top Kudoed Authors