Symptom
You are retrieving EmployeeTime data from SuccessFactors using OData API & filtering on the lastModifiedDateTime property:
- GET https://apisalesdemo2.successfactors.eu/odata/v2/EmployeeTime?$filter=lastModifiedDateTime ge '2023-02-01T03:32:00Z'
You observe in the response, that records with lastModifiedDateTime value outside of your filters are being returned:
- <d:lastModifiedDateTime m:type="Edm.DateTimeOffset">2023-02-01T02:32:50Z</d:lastModifiedDateTime>
Environment
- SAP SuccessFactors HXM Suite
- OData API
Cause
This behaviour is happening due to the fact that we are comparing a datetimeoffset (lastModifiedDateTime) to datetime (2023-02-01T03:32:00Z):
Properties of type: datetime & datetimeoffset are not the same & behave differently
- Generally for the lastModifiedDateTime of type: datetime the input value is in UTC time, and it is stored and retrieved from the database in the same format.
- Generally for the lastModifiedOn of type: datetimeoffset it takes time zone into account when converting dates
You can read more about this in the API documentation: Using the DateTime Format & Using the DateTimeOffset Format
Resolution
To make the above query filter behave as you expect, you would need to include datetimeoffset before the timestamp in the filter:
- GET https://apisalesdemo2.successfactors.eu/odata/v2/EmployeeTime?$filter=lastModifiedDateTime ge datetimeoffset'2023-02-01T03:32:00Z'
This ensures that we are comparing values of the same type
Another option is to instead use the lastModifiedDate property which is of type datetime:
- GET https://apisalesdemo2.successfactors.eu/odata/v2/EmployeeTime?$filter=lastModifiedDate ge '2023-02-01T03:32:00Z'
This ensures that we are comparing values of the same type
However as a general recommendation, we would suggest checking the property being filtered within the OData API Data Dictionary to determine it's type (datetime or datetimeoffset)
Then place the type before the timestamp value in the query to ensure same types are being compared
Keywords
EmployeeTime, lastModifiedDateTime, lastModifiedOn, datetime, datetimeoffset, filter, format , KBA , LOD-SF-INT-ODATA , OData API Framework , LOD-SF-EC-TIM , Time Off , LOD-SF-INT-EC , Employee Central SFAPI & OData Entities , Problem