5

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

9

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).

  • 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 '14 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 '14 at 12:43
  • And you can only apply a > to the querystring not < or anything else? – RSM May 27 '14 at 14:12
  • You can use < and >. I'm not sure of <= or >=. – wjervis May 27 '14 at 14:21
  • return pnp.sp.search(<SearchQuery>{ Querytext: "ContentTypeId:0x010100BF90C798634D0A48839E0B9D3E6A9118* AND AssignedToOWSUSER:.com AND EarningsDateOWSDATE gt datetime '2019-09-21T00:00:00Z'", SelectProperties: this.selectFields.split(','), RowLimit: rowLimit, TrimDuplicates: false, RowsPerPage: rowLimit, StartRow: startRow }) it's not working with refinable filters.can you please help me with this? – Akshay Sep 24 at 10:00
0

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

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.