Skip to Content

Operator "gt" incompatible with operand types "Edm.DateTime" and "Edm.DateTimeOffset"

Hello Experts,

I am using SuccessFactors adapter in SAP CPI to fetch data from an entity in SF. I have following query in the channel,

Operation: Query(GET) ResourcePath :<Entity Name>?$select=<List of fields to be selected>&$filter=(startDate gt datetimeoffset'1900-01-01T00:00:00Z' and endDate le datetimeoffset'9999-12-31T23:59:59Z') Path to edmx : edmx/performancemanager10_successfactors_com_odata_metadata.xml

When I run this iFlow, I get following error,

Operator "gt" incompatible with operand types "Edm.DateTime" and "Edm.DateTimeOffset"

I checked this blog also - https://archive.sap.com/discussions/thread/3767947
but still I am getting the same error.

Any idea how can I resolved this issue?

Thanks in advance,

Netrey

Add comment
10|10000 characters needed characters exceeded

  • Get RSS Feed

5 Answers

  • Best Answer
    Jun 12 at 08:12 AM

    Hi Guys,

    Thanks a lot for your responses and help. I found the solution to this issue and thought to share it here so that other SCN users can benefit.

    To filter based on dates, SAP provides two variables - "fromDate" and "toDate". Use the syntax as follows after you filter criteria

    &fromDate=<from date in YYYY-MM-DD format>&toDate=<to date in YYYY-MM-DD format>

    See example below,

    $filter=company eq '9000' and status eq 'active'&fromDate=2018-06-07&toDate=9999-12-31
    

    Thanks and Regards,

    Netrey

    Add comment
    10|10000 characters needed characters exceeded

  • Apr 04 at 04:56 PM

    Hello Netrey,

    Could you please share the Entity Name so that I can replicate your issue.

    Regards,

    Sriprasad Shivaram Bhat

    Add comment
    10|10000 characters needed characters exceeded

  • Apr 05 at 08:10 AM

    I'm only pointing out the obvious - with the startdate and enddate that you provided, I'm sure any records would definitely fall into that range, so why bother having this filter criteria at all?

    Anyway, regarding the error, I would suggest you remove the open and closing brackets () from the $filter criteria first. And try testing from Postman first to make sure your query is correct before even configuring it in the CPI flow.

    Add comment
    10|10000 characters needed characters exceeded

  • Apr 26 at 01:53 PM

    Hi Guys,

    I tried following syntax for the filter expression,

    $filter=(startDate gt datetime'1900-01-01T00:00:00')

    It worked partially, meaning that the message was processed successfully without any syntax error however historical data was still not fetched.

    I tried fetching the details using SFAPI. However, there also I am getting only active records as-on-date.

    Any other idea which will get me full historical details from the specified portlet/object?

    - Netrey

    Add comment
    10|10000 characters needed characters exceeded

  • May 03 at 10:52 AM

    Hello Netrey,

    Basically these 2 fields startDate and endDate are of type datetime so you cant use datetimeoffset while comparing.Below query should work for you.

    $select=userId,endDate,startDate&$filter=(startDate gt datetime'2017-12-08T00:00:00' and endDate le datetime'9999-12-31T23:59:59Z')

    let me know if you face any issues.

    Regards,

    Sriprasad Shivaram Bhat

    Add comment
    10|10000 characters needed characters exceeded