SharePoint 2013 OData $filter seems broken with date functions

Question

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.

Answer 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