36

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 + "'",
        ...
    });
Aaron
  • 732
  • 1
  • 9
  • 23
  • 1
    For the record, the correct field name in a SharePoint Calendar list is called 'EventDate'. 'StartDate' doesn't work. – Thomas Carpe Dec 01 '16 at 16:25

6 Answers6

39

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 ...
Paul Schaeflein
  • 5,048
  • 1
  • 20
  • 29
  • 7
    Unless I'm missing something, this doesn't seem to work in SharePoint 2013 on premises. Or do we need to be at a certain CU or something?

    I'm about to revert back to SPServices and CAML because I know I can get recurring events, etc. as well as filter by date(s).

     – Marc D Anderson Jun 19 '15 at 17:35
19

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

Community
  • 1
wjervis
  • 5,738
  • 24
  • 45
  • 1
    How can filter just by the Month? – naijacoder Nov 25 '14 at 06:37
  • @naijacoder How do you mean? You want items where date between March-2014 and July-2014? In that case just use the beginning and end dates of the month (2014-03-01 and 2014-07-31). – wjervis Nov 25 '14 at 11:51
  • 2
    You may also use a day-only format like YYYY-MM-DD, if you do not need a precision to the hour. – Dirk Feb 27 '19 at 22:42
5

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.

Eric Alexander
  • 43,293
  • 10
  • 53
  • 93
3

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

user52894
  • 31
  • 1
2

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");
    }
 });
user1913559
  • 180
  • 5
1

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() + "'"
Waqas Sarwar MVP
  • 57,008
  • 17
  • 43
  • 79
Kevin Hu
  • 11
  • 1