- SAP Managed Tags:
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
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!
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
| EDM.Time | stores time between 00:00:00 to 23:59:59 |
| EDM.DateTime | store values in the your DC server's local time in format 2022-06-02T00:00:00 |
| EDM.DateTimeOffset | store time values in UTC time zone in format 2022-06-02T00:00:00Z |
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!
6 Comments
| User | Count |
|---|---|
| 4 | |
| 3 | |
| 3 | |
| 3 | |
| 2 | |
| 2 | |
| 2 | |
| 2 | |
| 2 | |
| 1 |
