1

I am using SP2013 SP1.

I am trying to perform the following OData opertaion from a client app that contains an External Content Type. The ECT points to a ASP.Net OData service.

My Javascript code in the app makes a query call and the url looks like this:

http://xxx/_api/web/lists/getByTitle('People')/items?$filter=month(BirthDate)%20eq%201%20and%20day(BirthDate)%20eq%206

I am saying get the People that have a birthday with a month of 1 and a day of 15.

However, SharePoint returns:

The query is not valid.

at Microsoft.SharePoint.SPListItemEntityCollectionCamlQueryBuilder.SetWhereBinaryOp(XmlWriter writer, EdmParserNode parseNode) at Microsoft.SharePoint.SPListItemEntityCollectionCamlQueryBuilder.SetViewQuery(SPQuery query, XmlWriter writer, StringBuilder sb) at Microsoft.SharePoint.SPListItemEntityCollectionCamlQueryBuilder.BuildCamlQuery() at Microsoft.SharePoint.SPListItemEntityCollectionCamlQueryBuilder..ctor(SPList list, RESTfulQuery restQuery, Nullable1 itemId) at Microsoft.SharePoint.SPListItemEntityCollectionQuery..ctor(SPList list, RESTfulQuery restQuery, Nullable1 itemId) at Microsoft.SharePoint.SPListItemEntityCollection.TryWriteAsOData(ServerStub serverStub, ODataWriter writer, RESTfulQuery query, ProxyContext proxyContext) at Microsoft.SharePoint.Client.ServerStub.Write(Object value, Uri path, ODataWriter writer, RESTfulQuery query, ProxyContext proxyContext) at Microsoft.SharePoint.Client.Rest.RestRequestProcessor.Process() at Microsoft.SharePoint.Client.Rest.RestRequestProcessor.ProcessRequest() at Microsoft.SharePoint.Client.Rest.RestService.ProcessQuery(Stream inputStream, IList`1 pendingDisposableContainer)

I checked this url out http://msdn.microsoft.com/en-us/library/office/fp142385%28v=office.15%29.aspx and its seems like what I am doing should work, but it doesnt.

But if I run the exact same $filter query directly on the OData service I get the results I am looking for. Only when I go through SharePoints ECT and use any of the date functions in the $filter to I get an error.

Is this a bug in SharePoint 2013?

Also, I noticed that if I filter with a startsWith in the $filter its not returning all the matches. Is there a row limit that it searches. So if I have a startswith that should return 2 records but the second record is at the end of the list (say at the 1500th place) it doesnt seem to return it.

1

This is a limitation on SharePoint.

REST API filter by month

The workaround

  1. Use 2010 REST endpoint ListData.svc
  2. Create a calculated field, and then use CAML query

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.