2920680 - Using lastModifiedDateTime Range Filtering - SuccessFactors OData API

When querying an effective-dated entity (such as EmpJob or EmpCompensation) using the OData API with a filter on lastModifiedDateTime to retrieve records modified within a specific datetime range, you might receive a record that was not modified within that range.

For example, if you aim to retrieve only records modified in 2024, you might use:

• $filter=(lastModifiedDateTime gt (greater than) datetimeoffset'2024-01-01T00:00:00Z' AND lastModifiedDateTime lt (less than) datetimeoffset'2025-01-01T00:00:00Z')

Even though you verified that no modifications occurred within the specified time range, the query returns a record with a lastModifiedDateTime outside the range. 

NOTE: The same behaviour happen in Integration Center, as it is based on OData API resources.

SAP SuccessFactors HXM Suite

This behavior is specific to effective-dated entities and is expected due to how the OData API processes these records.

Key points:

• Effective-dated entities consist of multiple segments (each with a start date, end date, and a lastModifiedDateTime value for the segment).
• The filter condition (lastModifiedDateTime gt ... and lastModifiedDateTime lt ...) is evaluated across all segments rather than a single record. Specifically:
  • IF there exists any segment for the user with lastModifiedDateTime greater than the start time AND any segment (which could be a different one) with lastModifiedDateTime less than the end time, then the record is included.
• The record returned is determined by the effective dating parameters (asOfDate, fromDate, toDate). If none are provided, the query defaults to asOfDate=today, returning the current active segment, which may have a lastModifiedDateTime outside the specified range.

Important Distinction

• Effective-Dated Entities: The peculiar behavior described occurs due to the nature of these entities, where multiple segments are considered. The system may return a current active segment even if historical segments meet the filter criteria.
• Non-Effective Dated Entities: For non-effective dated entities, the query directly filters on the single record available, and the behavior is straightforward, without the complexities of multiple segments.

To filter records modified within a specific datetime range, the following approaches are recommended specifically for effective-dated entities:

Approach 1: Full Data Extraction and Delta Calculation

1. Implement an interface that retrieves all data for the effective-dated entity (by setting the effective date range from 1900-01-01 to 9999-12-31).
2. Store the extracted data in an internal staging table in your system.
3. In subsequent runs, compare the current data with the previously stored data to compute the changes (delta).

Approach 2: Middleware Filtering

1. Use an OData query to retrieve all records for the entity (by setting fromDate=1900-01-01 and toDate=9999-12-31).
2. After receiving the response, use middleware (such as CPI) to filter the records by lastModifiedDateTime (i.e., only records with lastModifiedDateTime within the desired range).
   • Note: This filtering must be done in the middleware or an external application, as it cannot be achieved within the OData query itself.

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 , How To