Filtering records based on date using OData API

(1) Share

Report

Posted on by Rajaneesh H

182

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)

Verified answer

Rajaneesh H 182 on at

Like (0)

Report

Filtering records based on date using OData API

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!!
Suggested answer

gdrenteria 12,183 Most Valuable Professional on at

Like (1)

Report

Filtering records based on date using OData API

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
Suggested answer

Rajaneesh H 182 on at

Like (0)

Report

Filtering records based on date using OData API

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

YUN ZHU 73,696 Super User 2024 Season 2 on at

Like (1)

Report

Filtering records based on date using OData API

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 182 on at

Like (0)

Report

Filtering records based on date using OData API

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

Shrey Chauhan 288 on at

Like (1)

Report

Filtering records based on date using OData API

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