3348259 - OData API - EmployeeTime/lastModifiedDateTime not filtering correctly

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>
  
  

• SAP SuccessFactors HXM Suite
• OData API

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

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

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