REST API filter by start and end dates using 'today'

Question

I'm new to filtering dates with REST in SP but am trying to do something like below but am not sure how to get today's date in as a variable into the request. I don't think I'm too far off so any help is greatly appreciated.

    var today = new Date();
    $.ajax({
        url: "/_api/web/lists/GetByTitle('DateItems')/items?$orderby=SortOrder asc&$filter=StartDate le datetime'" + today + "' and EndDate ge datetime'" + today + "'",
        ...
    });

Answer 1

Since your "today" variable is a JavaScript date object, you can get the string representation by calling the toISOString() method.

...$filter=StartDate ge datetime'" + today.toISOString() + "' and ...

Answer 2

I answered the question here (not exactly a duplicate, since that was in regards to the Search REST API, and I happened to give an answer for basic REST).

Here is how you filter dates in REST:

$filter=StartDate ge 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=(StartDate ge datetime'2014-01-01T00%3a00%3a00') and (EndDate le 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).

Answer 3

This article explains REST filtering based on date, http://itblog.wolthaus.net/2011/12/rest-filter-datetime/.

You'd want to pass in a date in YYYY-MM-DD format.

Answer 4

I was pulling the data for online Share-point 2013/ office 365. I used the above method. But It did not return the desired result. I had created the two sample record on with date "2 april 2016". When i use the above syntax, It always return me only one record. I had spent two days to get this work. I made the following addition in above filter.

$filter= (Start_Date ge datetime'2016-04-02T07:00:00.000Z') and (End__Date le datetime'2016-04-03T03:30:00.000Z')

You have to add .000Z to the end of your data-time string to convert the timezone. In the pattern, the inclusion of a 'z' date-time component indicates that timezone format needs to conform to the General time zone "standard", examples of which are Pacific Standard Time; PST; GMT-08:00.A 'Z' indicates that the timezone conforms to the RFC 822 time zone standard, e.g

Answer 5

If your situation is like mine and you are hosting an anonymous enabled site collect so listdata.svc is out of the question because it requires authentication; yes you can query the REST end point, albeit you'll need to do a POST request and use CAML.

Download U2U CAML Builder to construct your query and generate the CAML. From there do a post request to /GetItems (note the 'get' part, not just /items).

$.ajax({
    url: "/_api/lists/getbytitle('School-Calendar')/GETitems",
    method: "POST",
    data: "{ \"query\" : { \"__metadata\": { \"type\": \"SP.CamlQuery\" }, \"ViewXml\": \"<View><Query><Where><And><Geq><FieldRef Name='EventDate' /><Value IncludeTimeValue='TRUE' Type='DateTime'>2016-12-01T16:15:35Z</Value></Geq><Leq><FieldRef Name='EventDate' /><Value IncludeTimeValue='TRUE' Type='DateTime'>2016-12-31T16:16:31Z</Value></Leq></And></Where></Query><ViewFields><FieldRef Name='Title' /><FieldRef Name='Location' /><FieldRef Name='EventDate' /><FieldRef Name='EndDate' /><FieldRef Name='Description' /></ViewFields><QueryOptions /></View>\" } }",
    headers: {
        "X-RequestDigest": $("#__REQUESTDIGEST").val(),
        "Accept": "application/json; odata=verbose",
        "Content-Type": "application/json; odata=verbose"
    },
    success: function(data)
    {
        console.log("SUCCESS QUERY", data);
    },
    error:function()
    {
        console.error("ERROR DURING QUERY");
    }
 });

Answer 6

Heres my solution; My issue was that I missed the ' that you need for datetime'yourdateInISO'

here is a link if you want to read

//date for 30 days ago  
var pastDate = new Date(new Date().setDate(new Date().getDate() - 30)

_spPageContextInfo.webAbsoluteUrl + "/_api/web/Lists/GetByTitle('Invoice Archive')/Items?$filter= Created lt datetime'" + pastDate.toISOString() + "'"