1

I may be asking the wrong question here. In SharePoint I normally filter the list with Start Date <= Today and End Date >= Today to get a list of staff that is out today. Both dates are in Date/Time format. I'm trying to accomplish the same in REST.

EDIT 12/12/2017 I think the problem lies within the formatting. SharePoint adds +8 to my current time zone so when filtered for midnight, it returns the wrong items. Any workaround this?

Below is filtered for 12/12/2017 12:00 AM enter image description here

Currently using the following to filter today: 

var dataUrl = siteUrl + "/_api/web/lists/getbytitle('AbsenceRequest')/items?$filter=((EventDate le '" + new Date().toISOString() + "') and (EndDate ge '" + new Date().toISOString() + "') and Status eq 'Approved')&$select=SDID, calDisplayFN, EventDate, EndDate, RequestType, Status, ActivityType";

How do I ignore the time? EventDate and EndDate are both Date/Time format or filter by midnight?

I tried but returned an error (bad request):

var today = new Date();
today = moment(today).format("MM/DD/YYYY");
var currentDate = today+'T00:00:00.000Z';

var dataUrl = siteUrl + "/_api/web/lists/getbytitle('AbsenceRequest')/items?$filter=((EventDate le datetime '" + currentDate + "') and (EndDate ge datetime '" + currentDate + "') and Status eq 'Approved')&$select=SDID, calDisplayFN, EventDate, EndDate, RequestType, Status, ActivityType";
Improve this question
1

1 Answer 1

Reset to default
2

The correct date format to use in REST API filtering is YYYY-MM-DDT00:00:00.0000000Z.

var today = new Date();
today = moment(today).format("YYYY-MM-DD");
var currentDate = today + 'T00:00:00.0000000Z';

or 

var currentDate = new Date().getFullYear() + "-" + (new Date().getMonth() + 1) + "-" + (new Date().getDate()) + "T00:00:00.0000000Z";

Update - Tested the below url at my end and was able to reproduce your issue. After getting rid of the space before datetime. It works for me.

Make the REST API endpoint as:

var dataUrl = siteUrl + "/_api/web/lists/getbytitle('AbsenceRequest')/items?
$select=SDID,calDisplayFN,EventDate,EndDate,RequestType,Status,ActivityType
&$filter=((EventDate le datetime'" + currentDate + "') and (EndDate ge datetime'" + currentDate + "') and (Status eq 'Approved'))"
Improve this answer
9
  • It still gives me a bad request error and then freezes Edge (can't even close the tab). I removed the "datetime" which displays the datatable but incorrect data is being filtered. $filter=((EventDate le '" + currentDate + "') and (EndDate ge '" + currentDate + "') and Status eq 'Approved') I inserted "datetime" again and got the same error and behavior.
    – Tamras Merin
    Dec 12, 2017 at 18:15
  • can you check with edited code ?
    – Gautam Sheth
    Dec 13, 2017 at 3:39
  • I tried. Same result. when I have "datetime" in the filter -- the page displays blank datatable then a "bad request" error message. I removed the "datetime" and gotten incorrect result. Please see the image I posted on my initial question. I'm on SharePoint Online if it makes any difference. Thanks Gautam.
    – Tamras Merin
    Dec 13, 2017 at 16:59
  • I also tried it on a browser https://tenant.sharepoint.com/_vti_bin/ListData.svc/AbsenceRequest/?filter=EventDate le datetime '2017-12-13T00:00:00Z' and EndDate ge datetime '2017-12-13T00:00:00Z and it pulled some data. I just need to figure out what app to use to read easily than using Notepad. Thanks Gautum.
    – Tamras Merin
    Dec 13, 2017 at 17:14
  • 1
    ok have edited the code and got rid of extra space before datetime. It started working now. Notice have removed extra space and made it datetime'" instead of datetime '". Apologies :( . Ensure that your url is as /_api/web/lists/getbytitle('AbsenceRequest')/items?$select=SDID,calDisplayFN,EventDate,EndDate,RequestType,Status,ActivityType &$filter=((EventDate le datetime'" + currentDate + "') and (EndDate ge datetime'" + currentDate + "') and (Status eq 'Approved'))
    – Gautam Sheth
    Dec 14, 2017 at 6:47

Your Answer

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

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