Filtering querystring results by date range

Question

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

wjervis · Accepted Answer · 2014-05-27T13:55:30.330

11

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

edited May 27 '14 at 13:55

answered May 27 '14 at 12:15

wjervis

5,738
24
45

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 '19 at 10:00

score 0 · Answer 2 · edited May 27 '14 at 12:02

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

Filtering querystring results by date range

2 Answers2

Linked