CODEWITHSUNDEEP
restapidatetimesharepointsharepoint-online
AllramEst
AllramEst

Reputation: 1449

SharePoint REST Api filter on date

I am trying to filter out items by date. I want to get one item that has the dateFrom equel or bigger to today and dateTo equel or less then today. This is my query and reformat date function. In SharePoint Online I have the date set to only date. by default sharePoint sets the time part to 23:00:00. Thats why the static use of it. The ´ge dateFrom´ part of the query works fine but the le dateTo ignores the equel part. So if dateTo is set to 2018-03-19. I dont get any items returned with the dateTo set to that date 2018-03-19. Is my query wrong?

     var requestUrl = _spPageContextInfo.webAbsoluteUrl + "/_api/web/lists/getbytitle('" + _weaklyQuestion + "')/items?$select=Id,Title,answersOptions,dateFrom,dateTo&$filter=('" + ReFormatTime() + "' ge dateFrom) and ('" + ReFormatTime() + "' le dateTo)&$top=1";

        function ReFormatTime() {
        var date = new Date(),
            year = date.getFullYear(),
            month = date.getMonth() + 1,
            dt = date.getDate();

        if (dt < 10) {
            dt = '0' + dt;
        }
        if (month < 10) {
            month = '0' + month;
        }
        return year + '-' + month + '-' + dt + 'T23:00:00Z';
    }

Upvotes: 0

Views: 24742

Answers (2)

Amay Kulkarni
Amay Kulkarni

Reputation: 838

For SharePoint Online version - Date Range Comparison:

1) Convert dates to be use for comparison in 2019-04-18T23:59:00.000Z format.

2) Add 000Z at the end of the date and form rest query like below.

/_api/web/lists/getByTitle('ListName')/items?$orderby=Created desC&$filter=(Created ge datetime'2019-04-17T00:00:00.000Z') and (Created le datetime'2019-04-18T23:59:00.000Z')

Upvotes: 5

TLP
TLP

Reputation: 1288

You have to wrap your formatted time string inside a datetime() 

var requestUrl = _spPageContextInfo.webAbsoluteUrl + "/_api/web/lists/getbytitle('" + _weaklyQuestion + "')/items?$select=Id,Title,answersOptions,dateFrom,dateTo&$filter=(datetime('" + ReFormatTime() + "') ge dateFrom) and (datetime('" + ReFormatTime() + "') le dateTo)&$top=1";

Upvotes: 5

Related Questions