Filtering Items by Date Range using SharePoint 2013 REST API

For some reason I always had extra difficulty when filtering results by dates in SharePoint, especially when I needed a "between" filter. This time I needed to render a calendar on a page accessed anonymously. I used a FullCalendar jQuery plugin for this,  and the SharePoint 2013 REST API. In my implementation when the end user changes a month in the calendar, it calls a function passing it start and end dates of a date range. The function then needs to retrieve JSON from SharePoint corresponding to the date range.

I started with turning on Team Collaboration Lists feature on my publishing site and creating the Calendar list. Then I have got my REST endpoint, this time around with a "greater than or equal to" filter:

http://devserver2012/_api/web/lists/getbytitle('events')/items?$select=Id,Title,Description,EventDate,EndDate,fAllDayEvent&$filter=EventDate ge datetime'2013-06-08T00:00:00'
 
The reason for my blog post is that the above RESTful call doesn't work. Research has pointed out a few things:

1. I am not the only one. Other people have had the issue. Rob Windsor's comment was quite useful to me.
2. The MSDN guidance has a table of supported filtering operations. First of all, it incorrectly capitalizes the numeric comparison operations. For example, instead of "Ge" operator which does not work, I was using "ge" which does work. Second of all, the references pointing back at www.odata.org were unfortunately broken there, at the time of this writing at least.
3. Later I've learned that my filter actually works with a Boolean "and" operator as I will show below. Does this make it unsupported? It probably does, according to the mentioned MSDN article, although the article itself clearly needs a revision.
4. OData documentation is your friend. I find it hard to read, yet it is ultimately helpful. You can see query options supported by OData in section 4.5 on this page. Notice how the operators are all lowercase in the examples.  The formatting of dates in filter queries can be found here. This is where the datetime'2013-06-08T00:00:00' comes from. One interesting detail, although the format spec does not indicate this, the following works just as well: datetime'2013-06-08T00:00:00Z'. Apparently the ISO-8601 is still honored.

Getting back to my original REST call - what "fixes" it? If I were to use ListData.svc instead then I'd get the following:

http://devserver2012/_vti_bin/ListData.svc/Events?$select=Id,Title,StartTime,EndTime,AllDayEvent&$filter=StartTime ge datetime'2013-06-10' and EndTime lt datetime'2013-06-12'

The above call actually works, although it ignores time portion when filtering. You can see that I get my "between" filter in there, also the column names are different - it uses display names of the columns with removed spaces. Also notice how the date values are formatted, actually conflicting with the OData format spec. In general, if you examine the Atom results returned, you will also find that date formatting is relaxed (no "Z" Zulu time indicator).  So what! We can celebrate now... Well, almost - the call to ListData.svc won't work for anonymous users. Since this was a deal breaker to me, I kept on looking. Turning to the ULS log shows that the error message  "The field 'EventDate' of type 'DateTime' cannot be used in the query filter expression." comes from here:

Microsoft.SharePoint.SPListItemEntityCollectionCamlQueryBuilder.CheckFieldRefUsage(SPField field, FieldRefUsage fieldRefUsage)

With help of Reflector we see that the SPField.Filterable property is logically enough the driving force here. Then using SharePoint Manager to examine the value of this property for the EventDate ("Start Time") field of the Event content type we of course find that its Filterable property is set to false, and this is what is causing the error message to be returned. I should mention that there is little value in working around this behavior by using calculated fields - they are also rejected as filters by this method. To prove that it actually works in principle I've created a DateTime column from scratch and named it "Date3". Here is how my REST call now looks like:

http://devserver2012/_api/web/lists/getbytitle('events')/items?$select=Id,Title,Description,EventDate,EndDate,fAllDayEvent,Date3&$filter=Date3 ge datetime'2013-06-13T00:00:00Z' and Date3 le datetime'2013-06-14T00:00:00Z'

This works when accessing the list anonymously. Problem solved and lesson learned: do not rely on the OOB Calendar list for anonymously accessible events, or be prepared to add your own custom DateTime columns to that list.

At the time of this writing I was running SharePoint Server 2013, Enterprise CAL, March 2013 update.