31

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

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 ...
  • 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
14

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

  • How can filter just by the Month? – naijacoder Nov 25 '14 at 6: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
  • 1
    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 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.

  • is there a setting where we can change this to DD-MM-YYYY format? – Vignesh Subramanian Feb 1 '16 at 10:28
  • 1
    No, it uses the ISO date format, you must construct a date string in that fashion – Eric Alexander Feb 1 '16 at 11:53
  • Thanks Eric. I tried SomeDate>= 27-12-2015 in Dev environment, it worked, but in higher environments SomeDate>=27-12-2015 did not work. Can you please check out the question that I have added for this sharepoint.stackexchange.com/questions/168711 – Vignesh Subramanian Feb 1 '16 at 12:20
2

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

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");
    }
 });
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() + "'"

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.