How to use OData Date filter query to filter data from OData NetWeaver Gateway Service?

[Former Member]

Hello,

I am trying to use the OData Filter query. to filter data records from the OData Service, using Date range for filters.

Please check the below URL for the SAP's sample OData Service, with filter option.

Service Query URL :

https://sapes1.sapdevcenter.com/sap/opu/odata/sap/ZGWSAMPLE_SRV/SalesOrderCollection?$filter=Created... eq DateTime

It gives out following error 😆

<message xml:lang="en">'$filter ' is not a valid system query option</message>

Please let me know, if there is any mistake in the Query or is there anything that needs to be done on the
Backend  Service.

Thanks & Regards,

Suraj Kumar

[Jitendra_Kansal · Accepted solution]

Hi Suraj,

As you mentioned in your query, URL

https://sapes1.sapdevcenter.com/sap/opu/odata/sap/ZGWSAMPLE_SRV/SalesOrderCollection?$filter=Created... eq DateTime

I believe filter should be like

$filter=OrderDate eq datetime'2014-03-11T14:49:52'

Please check once.

Rgrds,

Jitendra

[Former Member · Accepted solution]

Hello Jitendra,

Thank you for your prompt reply.

By adding " ? " to your suggestion i.e. " $filter=OrderDate eq datetime'2014-03-11T14:49:52' ", I was able to get the result.

All this while, I was trying with "DateTime" instead of "datetime".

Had it not been to your reply, I wouldn't have got the solution.

I have got another issue. I tried to find records between a given date range say 2013-12-03T00:00:00 and 2013-12-24T00:00:00.

I tried using "and" with "ge" and "le" parameters, but I am not getting any result.

Could you please help me with this?

Thank you for your earlier solution.

Thanks & Regards,

Suraj Kumar.

[Jitendra_Kansal · Accepted solution]

Glad to know you are able to get the details.

I have got another issue. I tried to find records between a given date range say 2013-12-03T00:00:00 and 2013-12-24T00:00:00.

I tried using "and" with "ge" and "le" parameters, but I am not getting any result.

I never tried this but you can check

is this how you tried?

$filter=OrderDate ge datetime'2014-03-10T12:01:03' and OrderDate le datetime'2014-03-11T13:01:02'

Rgrds,

Jitendra

[Former Member · Accepted solution]

Hello Jitendra,

Yes,

" ?$filter=OrderDate ge datetime'2014-03-10T12:01:03' and OrderDate le datetime'2014-03-11T13:01:02' "

this is the way, I am trying to do it, for Date Range.

[Former Member · Accepted solution]

Hi Jitendra,

Once again, thank you for your help.

I am able to filter the records using a date range.

I am sharing the information, just in case anyone out there might need it.

The OData Filter Query for Date Range (i.e. with Two date values) will return the results only if the OData Back-end Service is modified to accommodate such filter requests.

This has to be done in ABAP.

Please refer the following links which talks more about the Back-end ABAP Service being modified to ensure that the service returns records of data, as per the date range filter query

http://scn.sap.com/thread/3170195

http://scn.sap.com/blogs/lindsay.stanger/2012/12/29/gateway-odata-calls-convert-ivfilterstring-to-it...

http://scn.sap.com/thread/3173146

Hope these links are helpful for those looking for OData Date Range filter query.

Do let me know.

Regards,

Suraj Kumar.