Filtering SharePoint Search by Calendar Dates

SharePoint, out of the box does not support filtering calendar dates.  The reason for this is that to search, they are set up as strings, thus running a comparision is not achievable.  Example: Let’s say you are using the Search API, and only want to show events that have not taken place yet.  There is nothing within search that will allow you to do this, without doing some tweaks.

In order to accomplish this, you will first need to set up a custom mapping for search to pick up the calendar date as a DateTime datatype.

1. In Central Admin, click on the Search Service Application

2. Click on Search Schema

3. Click “New Managed Property” and fill it out like the following.  Be sure to set the type as “Date and Time”. Then click OK.

Be sure to map the “ows_q_Date_EndDate” and “ows_q_Date__EndDate” properties.

4. Once the property has been mapped, you will need to run a Full Crawl in order for search to pick up the new property.

5. Then after the crawl has finished, and the new mapped datatype is available, you can run a query for new training events that have not yet taken place:

https://sharepoint.company.com/_api/search/query?querytext='*training* -path:"https://sharepoint.company.com/management/"'&refinementfilters='and(ContentTypeID:0x0102*,CalendarEndDate:range([Today],max,from="ge"))'&clienttype='AllResultsQuery'&rowlimit=10&sortlist='CalendarEndDate:descending'

A few things to note in the query:

  • ContentTypeID:0x0102* is the SharePoint content type for calendar events
  • [Today] will need to be replaced in your code with today’s actual date and time in string format, so: ‘2017-01-01’ (or DateTime.Now.ToString(“yyyy-MM-dd”))
  • This query also includes an optional exclusion (-path:”https://sharepoint.company.com/management/”), if you do not want a particular calendar or site included in the results.

This will return you calendar events matching the keyword, that have also not taken place yet.

Posted in SharePoint 2013.

Leave a Reply

Your email address will not be published. Required fields are marked *