2933028 - Using the DateTimeOffset and DateTime Formats - OData API

• What is the expected output for DateTimeOffset and DateTime?
• How to use datetime and datetimeoffset to pass values to the $filter query parameter.

NOTE: Image/data in this KBA is from SAP internal systems, sample data, or demo systems. Any resemblance to real data is purely coincidental.

• SAP Successfactors HCM Suite
  • OData API

Refer to Getting your time zones right page from our official Employee Central OData Reference Guide.

As described in the page, usually fields of type Edm.DateTimeOffset store time values in UTC time zone, and Edm.DateTime fields store values in the your DC server's local time. You can verify the type of an entity's field with the following methods:

• Checking the OData API Data Dictionary tool in your SF instance;
• Querying the entity's metadata;
• If you run a query in Atom format (XML), the type is included in the response.
  Example: d:lastModifiedDateTime m:type="Edm.DateTimeOffset">2021-01-05T15:16:23Z</d:lastModifiedDateTime>

Using datetime or datetimeoffset in the $filter statement to query data

Let's suppose your server's time zone is GMT-5 and consider the following User record:

                <d:userId>103230</d:userId>
                <d:lastModifiedDateTime m:type="Edm.DateTimeOffset">2021-01-05T15:16:23Z</d:lastModifiedDateTime>
                <d:lastModifiedWithTZ m:type="Edm.DateTimeOffset">2021-01-05T15:16:23Z</d:lastModifiedWithTZ>
                <d:lastModified m:type="Edm.DateTime">2021-01-05T10:16:23</d:lastModified>

Considering that we use 'datetime' format to filter by the lastModifiedDateTime field, with this query:

• https://apisalesdemo4.successfactors.com/odata/v2/User?$filter=userId eq '103230' and lastModifiedDateTime eq datetime'2021-01-05T10:16:23'

It may seem strange, but this will return the record above even though the value passed in the filter condition apparently does not match the value stored in the database. We passed the condition 'lastModifiedDateTime equals datetime'2021-01-05T10:16:23', but the value of lastModifiedDateTime in the database for this record is 2021-01-05-15:16:23.

The reason is that the lastModifiedDateTime field stores the value in UTC time zone, and the datetime format passed in the filter statement treats the value passed in the query URL as in the server's time zone. So before the server compares the value passed in the filter with the one in the database, it converts it into UTC. The query above is equivalent to the ones below, using the 'datetimeoffset' format:

• https://apisalesdemo4.successfactors.com/odata/v2/User?$filter=userId eq '103230' and lastModifiedDateTime eq datetimeoffset'2021-01-05T10:16:23-05:00'
• https://apisalesdemo4.successfactors.com/odata/v2/User?$filter=userId eq '103230' and lastModifiedDateTime eq datetimeoffset'2021-01-05T15:16:23'
  

To summarize, 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).

OData Operations - Date and Time

DateTime, DateTimeOffset, UTC, DateTime format, Date time, Date time offset, Date time off set, local timezone, SF, successfactors, zone, data , KBA , LOD-SF-INT-ODATA , OData API Framework , LOD-SF-INT , Integrations , LOD-SF-INT-API , API & Adhoc API Framework , LOD-SF-RCM-API , Webservices & APIs , How To