Filter by Date Range with Business Central Web API v2.0 - OData date filter syntax

by Steve Endow

I just spent 10 minutes trying to figure out the proper syntax for date filtering with Business Central Web API v2.0 endpoints, so I am documenting it here for future reference.

I don't regularly work with BC Web APIs these days, so when I have to dig back into them every 3-6 months, I'm almost always rusty and have a hard time remembering the URL format and the OData filter syntax used by Business Central Web APIs.

Today I was trying to filter the results returned from the generalLedgerEntries endpoint.  If I try to call that endpoint without a filter, Postman gives me an error because it is returning too much data.

The body of this response was too large to be saved

Fine, let's filter based on postingDate.  But I don't remember the OData filter syntax.  Unfortunately, the OData documentation has ZERO examples of filtering by date.

When I search for OData filter examples using dates, I see samples for other applications that look like this:

$filter=postingDate ge datetime'2022-01-01T00:00:00.000' and postingDate le datetime'2022-12-31T00:00:00.000'

Let me save you some time:  This OData filter format does NOT work with Business Central Web APIs.  

Here is the format to filter by date on a Business Central Web API v2.0 endpoint:

generalLedgerEntries/?$filter=postingDate ge 2021-01-01 and postingDate le 2022-12-31

Here's what it looks like in Postman:

And here is a sample encoded URL:

https://api.businesscentral.dynamics.com/v2.0/<tenant>/Sandbox1/api/v2.0/companies(<guid>)/generalLedgerEntries/?%24filter=postingDate%20ge%202021-01-01%20and%20postingDate%20le%202022-12-31

Key points:

1. Prefix the filter with ?
2. Standard Odata filter operators like gt and ge do work
3. Do not put apostrophes or quotes around the date values
4. Do not use any date conversion functions to format the date value

Steve Endow is a Microsoft MVP in Los Angeles.  He works with Dynamics 365 Business Central and related technologies.

You can also find him on Twitter and YouTube, or through these links:  links.steveendow.com