3

Greetings fellow sharepoint warriors.

I am trying to understand the way all the filtering goes and I've stumbled upon a problem.

I would like to get items from list where birthday falls in current week. I am using Monday-Sunday if this is any help.

I have a column, which is Date & Time.

$.ajax({
   url: _spPageContextInfo.webAbsoluteUrl + "/_api" + "/web/lists/getbytitle('Workers')/Items?$select=FirstName,Birthday,LastName", //THE ENDPOINT
   method: "GET",
   headers: { "Accept": "application/json; odata=verbose" },
   success: function (data) {
         //DO SOMETHING WITH DATA
   }
});

I am trying to figure out how to filter date exactly. I know that filtering in range is done as following

$filter=(StartDate ge datetime'2014-01-01T00%3a00%3a00') and (EndDate le datetime'2014-04-30T00%3a00%3a00'

But how would i use a column information in that specific filter? If that was possible i could calculate day and month seperately and use jquery current year to filter between this weeks birthdays.

EDIT:/ Tried this out

_api/web/lists/getbytitle('Workers')/Items?$select=FirstName,Birthday,LastName&$filter=(Birthday ge datetime'1980-01-01T00%3a00%3a00') and (Birthday le datetime'2014-01-01T23%3a59%3a59') and (Day eq '21')

This gives me error: The field 'Day' of type 'Calculated' cannot be used in the query filter expression.

Best regards.

1 Answer 1

2

i'd suggest using momentjs and calculating the exact date that you need for each, then query rest based on that.

somethings like

var monday = moment().startOf('week').add(1, 'days'); //beginning of Monday midnight

var sunday = moment().startOf('week').add(7, 'days').add(23, "hours"); //end of Sunday 11:00PM

Then use

"_api/web/lists/getbytitle('Workers')/Items?$select=FirstName,Birthday,LastName&$filter=(Birthday ge " + monday + ") and (Birthday le " + sunday + ")
3
  • But the fact that birthday is f.e. 7th semptember of 1980 won't be problem here? Or does this accept every year now?
    – Egert Aia
    Oct 15, 2016 at 19:01
  • ah, you may want to use that date and then something like monday.month(), monday.day() and moment().year() to ignore the year value.
    – Mike
    Oct 15, 2016 at 19:50
  • nvm, i used sharepoint 2010 endpoint and used odate month and day function combined with momentjs values and now it is working like a charm! Thanks!
    – Egert Aia
    Oct 15, 2016 at 20:11

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.