Take the 2-minute tour ×
SharePoint Stack Exchange is a question and answer site for SharePoint enthusiasts. It's 100% free, no registration required.

Simply,

How do you filter results returned using querystring that fit within a given date range.

For example I have a custom property RMExpiryDate and the value could be any date, lets say for example the format is 2014-01-01.

How would I get it to return all results where RMExpiryDate falls between a given start date and a given end date?

In JS I am simply calling a call to a url:

var queryUrl = "http://mydomain.com/sites/blabla/_api/search/query?querytext='ContentType:Factsheet RMExpiryDate:2014-05-30'&rowlimit=500&SelectProperties=RMExpiryDate,fileExtension,filename,ContentType";

$.ajax({ url: queryUrl, method: "GET", headers: { "Accept": "application/json; odata=verbose" }, success: onQuerySuccess, error: onQueryError });

This returns all the results with an exact expiry date of the one set

share|improve this question
add comment

2 Answers 2

up vote 3 down vote accepted

I just realized that you are using the Search API. The example below is for basic SharePoint REST API. The following works for search:

Created>2014-01-01-T00:00:00

So for your case:

var queryUrl = "http://mydomain.com/sites/blabla/_api/search/query?querytext='ContentType:Factsheet RMExpiryDate>2014-05-30'&rowlimit=500&SelectProperties='RMExpiryDate,fileExtension,filename,ContentType'";

Should return items where RMExpiryDate is later than 31-May-2014.

I did notice that your SelectProperties are not enclosed with single quotes ('). I know that if I were to put that URL in the browser, I'd get an HTTP 400 Bad Request error.

This is for basic SharePoint REST API:

To filter by a date, you would use the following:

$filter=Created gt datetime'2014-01-01T00%3a00%3a00'

This filters for items created after 01-Jan-2014, 00:00:00.

So to filter between two date ranges, simply add another condition to the filter:

$filter=(Created gt datetime'2014-01-01T00%3a00%3a00') and (Created lt datetime'2014-04-30T00%3a00%3a00')

This filters for items created between 01-Jan-2014 and 01-May-2014.

Just create your dates in the format: YYYY-MM-DDTHH:MM:SS, and then encode it (or simply use %3a instead of the colons).

share|improve this answer
    
Hi thanks for the info. How would I use this and apply to a querystring parameter? Sorry but Im just getting into this so excuse and naievity. –  RSM May 27 at 12:40
    
How are you making the REST call? JavaScript? C#? Can you put your code up, and I'll show how to do what is necessary. –  wjervis May 27 at 12:43
    
thanks, please see update –  RSM May 27 at 12:56
    
And you can only apply a > to the querystring not < or anything else? –  RSM May 27 at 14:12
    
You can use < and >. I'm not sure of <= or >=. –  wjervis May 27 at 14:21
add comment

If i understand your query properly , then you can use following caml query to filter results.

query.ViewXml = @"<View>" +
"<Query>" +
    "<Where> <And>" +
    "<Geq>" +
        "<FieldRef Name='DueDate'/>" +
        "<Value Type='DateTime' IncludeTimeValue='FALSE'>" + startDateFx + "</Value>" +
    "</Geq>" +
    "<Leq>" +
        "<FieldRef Name='DueDate'/>" +
        "<Value Type='DateTime' IncludeTimeValue='FALSE'>" + endDatFx + "</Value>" +
    "</Leq>" +
    "</And> </Where>" +
"</Query>" +
"</View>";

Please note that the dates are formatted as below:

string startDateFx = startDate.ToString("yyyy-MM-ddTHH:mm:ssZ");

string endDatFx = endDate.ToString("yyyy-MM-ddTHH:mm:ssZ");

Also refer: http://www.codeproject.com/Articles/414414/SharePoint-Working-with-Dates-in-CAML http://social.msdn.microsoft.com/Forums/sharepoint/en-US/fed59f8e-72e2-46e2-9329-460fd65d7536/caml-query-datetime?forum=sharepointdevelopmentlegacy

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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