Filtering SAP API data based on Date or time fields

With S/4HAHA and Fiori, OData and APIs have become an integral part of almost all our solutions. And while working with them, we always encounter one or the other new issue and with that eventually we end up with new learning.

In this blog i am going to share my learnings on handling date and time fields in API/OData filters. You must be thinking what’s so different about them and that’s exactly i also thought about it to start with.

And we are going to see what is different.

Let’s say we want to use the API – Accounting Document Read and want to fetch the data filtered on Creation Time field.

Test the API in API Hub sandbox environment with the time field in filter and you will get the error

“Invalid parametertype used at function ‘eq’ (Position: 14)” as shown below

Findings and solution:

The date and time fields could be of three defined types

To check the fields defined type, we can check the property details in the Backend or check the service metadata

Now, if we want to filter the Edm.Time type field, we need to append time before the filter value in the URI as shown below and is would like a charm

cool isn’t it 🙂

DateTime format

In case the field is of type Edm.DateTime, then we can filter on this field by appending datetime in-front of the filter value like  datetime‘2022-06-02T00:00:00′ as shown below

Similarly, we can use datetimeoffset to filter field of type Edm.DateTimeOffset

Summary

As a best practice, it’s recommended to use ‘datetimeoffset’ format when filtering by fields that are of type datetimeoffset (Edm.DateTimeOffset), and ‘datetime’ format when filtering by fields of type datetime (Edm.DateTime).

We can use datetime to filter the field of type Edm.DateTimeOffset, it will not give error, but it might return weired results if your server time zone is different from UTC time zone.

Keep Learning & Keep Sharing!