2920680 - OData API: Filtering records by a 'lastModifiedDateTime' range (combining 'lt' or 'le' and 'gt' or 'ge') yields an unexpected result

You're querying a specific user's effective dated entity (such as EmpJob) to get records that were modified between two datetimes, similar to the one below:

• https://XXXX/odata/v2/EmpJob?$filter=lastModifiedDateTime gt datetimeoffset'2020-04-21T07:38:00Z' and lastModifiedDateTime lt datetimeoffset'2020-04-21T08:38:00Z' and userId eq '11111111'

You know by checking the user profile's Job Information history that no modifications were done within the provided time range, and therefore the query's response should be empy. However, a record is returned with lastModifiedDateTime outside the specified range.

• SAP SuccessFactors HXM Suite

• OData API

• Integration center

This is expected behavior. In short: it is not possible to combine two 'lastModifiedDateTime' filters to represent a time interval. Even if it seems to make sense semantically, the API will not interpret it as a range. The key idea to understand this is that the 'lastModifiedDateTime' filter looks at all the effective dated records of an employee as one object of analysis.

Although the query's semantic structure suggests the following logic:

• IF ANY of the employee's records has 'lastModifiedDateTim' greater than provided date AND lesser than the other provided date, return true.

The actual logic is as below:

• IF ANY of the employee's records has 'lastModifiedDateTime' greater than provided date AND ANY of the employee's records has 'lastModifiedDateTime' lesser than the other provided date, return true.

So, for all the records related to a specific user, it can find one whose LMD>2020-04-21T07:38:00Z and it can find another one whose LMD<2020-04-21T08:38:00Z.

Which record(s) are returned will be determined by the 'asOfDate' or 'fromDate' and 'toDate' parameters you are passing to the query. If none of there parameters are being passed (as is the case for the query used as example in this KBA), the query implicitly passes 'asOfDate'=today and the employee's current active record is returned. See Effective Dating Query in OData for more information.

If you want only the records that were modified between two datetimes, it will not be possible to achieve that only with an OData query. You'll need to build an external application that processes the queried data. Here are two possible approaches:

Suggestion 1)

1. Implement one interface that send all the EC entity data every execution ("Effective From" = 1900-01-01 and "Effective To" = 9999-12-31).
2. Store the values received in some internal staging table.
3. Build an additional logic in your other system, doing a comparison between the internal staging table with the values received from the last run, calculating the "Delta" to see the changes from the last run.

Suggestion 2)

1. Build one OData interfaces using the filters "Effective From" = 1900-01-01 and "Effective To" = 9999-12-31 (fromDate=1900-01-01&toDate=9999-12-31) to retrieve all the registers from the entity.
2. After receiving the OData Response, build an additional logic in the middleware like Dell Boomi or HCI to filter out only the registers with "Last modified Date Time" greater than the last run. 
3. This logic can be implemented in the middlewares, but not in the OData itself or Integration Center.

SAP SuccessFactors Employee Central OData API: Reference Guide

EC entities time based filters in Integration Center

gt, ge, le, lt, greater than, lesser than, greater or equal to, lesser or equal to, datetime, last modified date, lastmodifieddate, time-based, effective-dated, not working, wrong results , KBA , LOD-SF-INT-ODATA , OData API Framework , LOD-SF-INT , Integrations , Problem