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

[former_member455910]

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

[former_member455910 · Accepted solution]

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

[former_member53820]

Hi,

Can we call header value for fromDate & toDate?

[Sriprasadsbhat]

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

[former_member455910]

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

[engswee]

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.

[former_member455910]

Hi Eng Swee Yeoh,

I am using this filter criteria because by default OData returns only the currently active record. I want to fetch all historical records. If there is any alternate way to do this then I would be glad to know that as well.

Regards,

Netrey

[engswee]

I don't think that's the default behavior for OData.

Anyway, as I suggested above, try it out using Postman first.

And try omitting the brackets.

[Sriprasadsbhat]

Hello Netrey,

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

Regards,

Sriprasad Shivaram Bhat

[former_member455910]

Hello Sriprasad Shivaram Bhat,

We are using entity "EmpPayCompRecurring"

Thanks,

Netrey