Filtering records based on date using OData API

(1) Share

Report

Posted on by Rajaneesh H

248

Hi Team,

How do I filter records based on DATE in OData API in Business Central.

I have a API where I need to filter on postingdate. However when tried eq, gt, lt the result is not filtering properly.

Eg : http://xxx:7048/BC220/api/v2.0/companies(xxxx)/journals(xxx)/journalLines?$filter=postingDate eq 2024-01-10

Above API is not returning any records. However record is available for the mentioned posting date.

Thank

All responses (6)

Answers (1)

Sort by

Suggested answer

Shrey Chauhan 297 on at

Like (1)

Report
Copy link

Link copied!

Hello,

Here's the correct filter to get the data related to specific posting date :

Hope this help's to solve your queries.

May other expert's give you better solution's.

Thank You,
Shrey Chauhan
Suggested answer

Rajaneesh H 248 on at

Like (0)

Report
Copy link

Link copied!

Hi Shrey & others,

I have done the same thing as you mentioned but still not getting the expected result. see the screenshot below. Date is not filtering properly for 2024-01-02.
Suggested answer

YUN ZHU 103,085 Super User 2026 Season 2 on at

Like (1)

Report
Copy link

Link copied!

Hi, this is a bit strange, the method mentioned by Shrey should have no problem. I also tested it.

Endpoint: https://api.businesscentral.dynamics.com/v2.0/c8558e71-6ad8-4fcd-9f29-3ca165b41bbe/Sandbox223/api/v2.0/companies(554f4f15-9c8e-ee11-be3e-6045bde96b9a)/salesOrders?$filter=postingDate eq 2023-04-02

Since this is the On-Pre version, I suspect this may be a date format issue with the server, but I'm sorry I'm not sure.

Hopefully other experts can give you better advice.

Thanks.

ZHU
Suggested answer

Rajaneesh H 248 on at

Like (0)

Report
Copy link

Link copied!

Hi Zhu,

Thanks for the response. Is there any way we can format the date in the API? If yes, can you suggest me any document link or any other video link?

I also noticed that, if I filter for 2024-01-10 date , it is displaying 2024-01-09 records. i.e., day-1 records!

Thanks Again

Rajaneesh H
Suggested answer

Gerardo Rentería Ga... 27,621 Super User 2026 Season 2 on at

Like (1)

Report
Copy link

Link copied!

Hi

I hope this can help

https://community.dynamics.com/forums/thread/details/?threadid=d6280efa-f8c6-4da4-a71f-269d0356735d

https://docs.oasis-open.org/odata/odata/v4.01/odata-v4.01-part2-url-conventions.html#sec_DateandTimeFunctions

Best Regards

Gerardo
Verified answer

Rajaneesh H 248 on at

Like (0)

Report
Copy link

Link copied!

Hi All,

After doing some permutations and combinations, I finally able to crack some solution for this problem which is occurring especially in On-Premise BC.

Instead of filtering date like this

http://xxxx:7048/B220/api/v2.0/companies(xxxxx)/journals(xxx)/journalLines?$filter=postingDate eq 2024-01-02

try this

http://xxxx:7048/B220/api/v2.0/companies(xxxxx)/journals(xxx)/journalLines?$filter=postingDate eq 2024-01-02T10:00:00.00Z

it worked!! I have tested several scenarios including range filters and tests passed.

Happy Coding!!