Skip to Content
0

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

Apr 04 at 01:42 PM

113

avatar image

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

10 |10000 characters needed characters left characters exceeded
* Please Login or Register to Answer, Follow or Comment.

5 Answers

Best Answer
Netrey Powdwal Jun 12 at 08:12 AM
0

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

Share
10 |10000 characters needed characters left characters exceeded
Sriprasad Shivaram Bhat
Apr 04 at 04:56 PM
0

Hello Netrey,

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

Regards,

Sriprasad Shivaram Bhat

Show 1 Share
10 |10000 characters needed characters left characters exceeded

Hello Sriprasad Shivaram Bhat,

We are using entity "EmpPayCompRecurring"

Thanks,

Netrey

0
Eng Swee Yeoh
Apr 05 at 08:10 AM
0

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.

Show 2 Share
10 |10000 characters needed characters left characters exceeded

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

0

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.

0
Netrey Powdwal Apr 26 at 01:53 PM
0

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

Share
10 |10000 characters needed characters left characters exceeded
Sriprasad Shivaram Bhat
May 03 at 10:52 AM
0

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

Share
10 |10000 characters needed characters left characters exceeded