2310920 - Datetime Filter not Working Using OData Service

SAP Knowledge Base Article - Public

2310920 - Datetime Filter not Working Using OData Service

Symptom

Require to filter data on the basis of CreatedOn (Which is DateTime type), but getting below error message.

Error: <message xml:lang="en">Invalid token detected at position 51</message>

Reproducing the Issue

Open the below URL in Google Chrome and enter the required credentials:

https://my<tenantid>.crm.ondemand.com/sap/c4c/odata/v1/c4codata/AccountCollection?$filter=CreatedOn eq datetime'2013-01-24T10:09:19.6938540Z'

you receive the below error:

Error: <message xml:lang="en">Invalid token detected at position 51</message>

Resolution

The correct filter URLs are as follows:

1. For a particular datetime
https://my<tenantid>.crm.ondemand.com/sap/c4c/odata/v1/c4codata/AccountCollection?$filter=CreatedOn eq datetimeoffset'2016-05-03T10:09:19.6938540Z'

2. For a particular date for eg: 3/5/2016
https://my<tenantid>.crm.ondemand.com/sap/c4c/odata/v1/c4codata/AccountCollection?$filter=CreatedOn eq datetimeoffset'2016-05-03T10:09:19.6938540Z' and CreatedOn le datetimeoffset'2016-05-04T00:00:00.0000000Z'

3. Between a Range for eg: 1/5/2016 to 3/5/2016
https://my<tenantid>.crm.ondemand.com/sap/c4c/odata/v1/c4codata/AccountCollection?$filter=CreatedOn ge datetimeoffset'2016-01-01T00:00:00.0000000Z' and CreatedOn le datetimeoffset'2016-05-04T00:00:00.0000000Z'

Note: In the above example Accountcollection BO is used, however same is applicable for other BO as well except Visit Collection. As StartDateTime is a complex property if you query with Visit Collection. Hence you need to use the below URL to query the property StartDateTime with Visit Collection.

https://<Your Tenant>/sap/c4c/odata/v1/c4codata/VisitCollection?$f ilter=StartDateTime/timeZoneCode ge datetimeoffset'2013-10-25T18:00:00.0000000Z'&$top=2

https://<Your Tenant>/sap/c4c/odata/v1/c4codata/VisitCollection?$f ilter=StartDateTime/content eq '272013-10-25T18:00:00.0000000Z'

 

 

 

 

Keywords

DateTimeOffset , Date time, OData , Visit Collection Date time , Date time filter , KBA , LOD-CRM-EMP , Employee , How To

Product

SAP HYBRIS C4C 1705 ; SAP HYBRIS C4C 1802 ; SAP HYBRIS C4C 1805 ; SAP Hybris Cloud for Customer 1605 ; SAP Hybris Cloud for Customer 1608 ; SAP Hybris Cloud for Customer 1611 ; SAP Hybris Cloud for Customer 1702 ; SAP Hybris Cloud for Customer 1708 ; SAP Hybris Cloud for Customer 1711