2310920 - Datetime Filter not Working Using OData Service

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>

SAP Cloud for Customer

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>

Incorrect OData query used for the Service.

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?$filter=StartDateTime/timeZoneCode ge datetimeoffset'2013-10-25T18:00:00.0000000Z'&$top=2

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

The usage of datetime and datetimeoffset can be found out from the metadata of the service using the annotation "Type".

If the annotation Type of the Property is Edm.DateTimeOffset then use datetimeoffset in the filter query.
If if the annotation Type of the Property is Edm.DateTime use datetime in the filter query.

Reference examples of properties of OpportunityCollection in c4codataapi:

<Property Name="CreationDateTime" Type="Edm.DateTimeOffset" Nullable="false" Precision="7" sap:creatable="false" sap:updatable="false" sap:filterable="true"/>

<Property Name="ProcessStatusValidSinceDate" Type="Edm.DateTime" Nullable="true" Precision="0" sap:creatable="true" sap:updatable="true" sap:filterable="true" sap:display-format="Date"/>

DateTimeOffset , Date time, OData , Visit Collection Date time , Date time filter , KBA , LOD-CRM-INT-API , OData API (C4C Only) , How To