SharePoint List Filter Query By UTC Date Not Valid

Question

I am having an error with my filter with other people located in separate time zones. It was recommended to me to switch to filtering with a UTC date instead of an ISO date and that should fix the issue.

Below is an example of my working filter function:

function openEditForThisUserThisWeek() {
    
    // we already know we can get the current user ID
    var userTitle = _spPageContextInfo.userDisplayName;
    
    // forget about moment, you can do whatever you need to do
    // with native javascript Date objects
    
    var today = new Date();
    
    // setting hours, minutes, seconds and milliseconds
    // to zero essentially sets it to "midnight"
    //today.setHours(0,0,0,0);
    today.toISOString().slice(0, 10) + "T00:00:00.000Z"
    // get monday of this week (at midnight)
    // NOTE - as mentioned, if this does not accurately
    // account for time zone offset, you might need to 
    // change this to sunday by changing the "1" to "0"
    var monday = new Date(today);
    monday.setDate(today.getDate() + (1 - today.getDay()));
    
    // get friday of this week (at midnight - accounts for all of friday)
    var friday = new Date(today);
    friday.setDate(today.getDate() + (5 - today.getDay()));
    
    // in order to make the REST query, we need ISO strings
    var mondayISO = monday.toISOString();
    var fridayISO = friday.toISOString();
    
    // when building the filter, for comparison operators,
    // "eq" means "equals", "ge" means "greater than or equal to",
    // and "le" means "less than or equal to"
    var str = fixedEncodeURIComponent(userTitle);
    
    var requestUri = _spPageContextInfo.webAbsoluteUrl + "/_api/web/Lists/getByTitle('Report')/Items?$\
        filter=User/Title eq '" + str + "'&$\
        select=*";
    
    
    function fixedEncodeURIComponent(src) {
    return encodeURIComponent(src).replace(/[']/g, function (c) {
            return '%' + c.charCodeAt(0).toString(16) + '%' + c.charCodeAt(0).toString(16);
        });
    }
    
    var filter = "$filter=User/Title eq '" + str + "' and Monday ge datetime'" + mondayISO + "' and Friday1 le datetime'" + fridayISO + "'";
    console.log(mondayISO);
    console.log(fridayISO);
    // use the filter as part of the overall query URL
    var queryUrl = _spPageContextInfo.webAbsoluteUrl + "/_api/web/lists/GetByTitle('Report')/items?" + filter;
    console.log(queryUrl);
    // don't bother with the "success" parameter, just chain it
    $.ajax({
        url: queryUrl,
        method: 'GET',
        headers: {
            accept: 'application/json;odata=verbose'
        }
    }).done(function(response) {
        if (response.d.results.length > 0) {
            // keep in mind that i am only checking that the
            // filter found more than zero results - more than zero
            // could be _one_, which is what we are expecting, OR
            // it could be MORE THAN ONE, which is _not_ what we are
            // expecting, but i'm not caring about that here.  if you care
            // that there is more than one item for the current user for this
            // week, then you will need to add additional code here to deal with
            // that eventuality. but here i am just going to take
            // the first result and run with it.
            var reportEntry = response.d.results[0];
            console.log(reportEntry);
            openDialog('/sites/Projects/Report/Lists/Report/EditForm.aspx?Id=' + reportEntry.ID);
    
        } else {
            // the length of the response array
            // must have been zero, which means nothing
            // matched the filter parameters, which in turn
            // means nothing was found for the current
            // user for this week
            alert('No Report Item was found for the current user for this week.');
        }
    }).fail(function (jqXhr, code, err) {
        // something went wrong with the GET request
        console.log(err);
        console.log(code);
        console.log(jqXhr);
    });
    }

When I console log the queryUrl, it is successfull and the following is logged:

eq 'User%20Name' and Monday ge datetime'2022-03-07T05:00:00.000Z' and Friday1 le datetime'2022-03-11T05:00:00.000Z'

When I change the mondayISO and fridayISO dates to the following and tack the filter on the end of my request url:

    var mondayUTC = monday.toUTCString();
    var fridayUTC = friday.toUTCString();

    var filter = "$filter=User/Title eq '" + str + "' and Monday ge datetime'" + mondayUTC + "' and Friday1 le datetime'" + mondayUTC + "'";

I get the following error in log:

The expression \"User/Title eq 'User Name' and Monday ge datetime'Mon, 07 Mar 2022 05:00:00 GMT' and Friday1 le datetime'Fri, 11 Mar 2022 05:00:00 GMT'\" is not valid."

UPDATE (in regards to Denis answer and Dylan's comment) I still get following alert No Report Item was found for the current user for this week.

I changed the following as recommended (see original function for full edit):

    var today = new Date();
    
    today.toISOString().slice(0, 10) + "T00:00:00.000Z"

    var monday = new Date(today);
    monday.setDate(today.getDate() + (1 - today.getDay()));
    
    var friday = new Date(today);
    friday.setDate(today.getDate() + (5 - today.getDay()));

    var mondayISO = monday.toISOString();
    var fridayISO = friday.toISOString();

To add a bit more insight, the Monday and Friday1 columns it is filtering for based on those dates are Date & Time Columns with the following calculated values:

Monday: =(2-WEEKDAY(Today)+(Today)) Friday1: =(6-WEEKDAY(Today)+(Today))

UPDATE 03-31-2022

I came across the following article: Timezone Issues when working with dates in SharePoint's Rest Services

The issues discussed is very similar to that of mine, but the function referenced that solved the issue isn't explained well. Where does the self come from? Also, would the input date in this case be the JavaScript generated date, or the date pulled from SharePoint? Or both?

self.toUtc = function(inputDate) {
    var localTime = inputDate.getTime();
    var localOffset = inputDate.getTimezoneOffset() * 60000;
    var utc = localTime + localOffset;
    var retval = new Date(utc);
    debugger;
    return retval;
};

Answer 1

It was recommended to me to switch to filtering with a UTC date instead of an ISO date and that should fix the issue.

You are already using UTC. toISOString() already returns a valid date format that uses UTC. No need to change this part of your code.

The issue with your solution is in these lines:

var today = new Date();
today.setHours(0,0,0,0);

today will be different based on the local time zone of each user. And setHours(0,0,0,0) will actually set a date to 0 hours according to a local time zone.

So, I don't know your specific situation, but if you want to make sure all users get the same date in the filter, then you can use the following technique:

today.toISOString().slice(0, 10) + "T00:00:00.000Z"

This essentially, removes your local time zone and makes sure this date is set to 0:0:0:0 according to the UTC, not the local time zone. Of course, it might cause unwanted consequences, so you might need to pick a "primary" time zone that is not UTC and use it as a base.