SAP Knowledge Base Article - Public

2982901 - SFAPI Compound Employee error Invalid SFQL! Error: Invalid WHERE clause. Function "to_Date" or "to_DateTime" is necessary for "last_modified_on" after 2H 2020 release

Symptom

You are running SFAPI CompoundEmployee queries like the sample below and you are getting the respective error in the response.

The query executed was in FULL MODE and you observed this error in 2H 2020 (b2011) release.

Request:

  • SELECT person,email_information,employment_information,job_information,personal_information FROM CompoundEmployee WHERE last_modified_on>=('2020-10-01T00:00:00Z')

Response:

  • Invalid SFQL! Error: Invalid WHERE clause. Function "to_Date" or "to_DateTime" is necessary for "last_modified_on". Usage Example: to_date('01/07/20', 'DD/MM/YY') or to_DateTime('2020-06-07T01:00:00Z')

Environment

  • SAP SuccessFactors HXM Suite
    • CompoundEmployee API in Full Mode

Cause

In the 3 months restriction development, our SFAPI engineering team aligned the API code between SFAPI CE DELTA and FULL Modes.

Prior to 2H 2020, the API code in FULL MODE accepted the format WHERE last_modified_on>=('2020-10-01T00:00:00Z'), but this format was never accepted in the DELTA mode.

Our handbook mention the correct format prior 2H 2020 in the chapter 2.8.2.3.1 Select Parameters for the WHERE Clause:

For example: WHERE last_modified_on > to_datetime('2012-11-06T23:30:00Z') WHERE last_modified_on > to_date('01/07/10', 'DD/MM/YY')

Resolution

Please adapt your integrations to starting using the supported formats in the SFAPI CompoundEmployee FULL MODE queries:

Option 1)

WHERE last_modified_on > to_datetime('2012-11-06T23:30:00Z')

Option 2)

WHERE last_modified_on > to_date('01/07/10', 'DD/MM/YY')

Keywords

  • Invalid SFQL! Error: Invalid WHERE clause. Function "to_Date" or "to_DateTime" is necessary for "last_modified_on". Usage Example: to_date('01/07/20', 'DD/MM/YY') or to_DateTime('2020-06-07T01:00:00Z'), SFAPI CompoundEmployee, CE, SFAPICE, 2H 2020 SF release, b2011, 2011, query, full mode, delta, last_modified_on
, KBA , LOD-SF-INT-CE , Compound Employee API , LOD-SF-INT , Integrations , Product Enhancement

Product

SAP SuccessFactors HCM Suite all versions