1

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;
};
10
  • 1
    To reply to your update - at this point I would go in and look at the what the data in the SharePoint list items in question actually is, and figure out why it's not matching what you are expecting to find. So like, go to the list, filter for all the items for the user that is having the issue, sort by Created or Modified descending so you get the newest ones first, then add the ID column to the view so you find the IDs of a couple of the newer items that are problematic. Then retrieve those items by ID through REST, either by writing another little script, or some other tool to help... Mar 10, 2022 at 15:20
  • 1
    ...make REST queries (I use Postman). Then once you have retrieved one of the list items you think should be matching your query, look at what the actual values of the Monday and Friday fields are, and figure out what you have to do from there. Mar 10, 2022 at 15:22
  • Dylan, so I just did what you recommended. All dates, even for the user in the west coast time zone are posting as Monday: "2022-03-07T05:00:00Z" and Friday1: "2022-03-11T05:00:00Z" atleast that is what it is showing for me. I am wondering, if because his local is T05:00:00Z if I can set them to 0 through the SharePoint column if that would fix it, just not sure how to do so
    – BeerusDev
    Mar 10, 2022 at 16:05
  • But in your own example where you console logged the query URL your dates have the same time zone offset, your log shows datetime'2022-03-07T05:00:00.000Z' which is also a 5 hour offset. So... maybe the dates are not the part of the filter settings that is making the query not find anything? Mar 10, 2022 at 16:27
  • 1
    Actually, I think I might see what the problem might be. In my answer to your other question I had suggested querying by user ID, but I see you are filtering for User/Title. But it doesn't look like you are $expanding the User field. To filter by lookup properties you also have to expand the field, so try adding &$expand=User. BUT - keep in mind that to use $expand=User you are also probably going to have to add a specific $select=User/Title, which then in turn means you are going to have to explicitly $select any other fields you want to retrieve. Mar 10, 2022 at 16:37

1 Answer 1

2

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.

3
  • 2
    You beat me to it, I was in the middle of writing an answer to try to explain the difference between ISO formatting and what UTC actually is. Full disclosure, that code is pretty much copy-pasted from my answer to this other question, BUT IN MY DEFENSE if you read my comments I do indicate that it might not account for time zone offset issues. XD Mar 10, 2022 at 3:50
  • 1
    Also, rather than string manipulation I was going to suggest today.setMinutes(today.getMinutes() - today.getTimezoneOffset()) to get UTC midnight. Mar 10, 2022 at 3:52
  • I tried both suggestions, please see my updates to the question. I added a bit more specificity. I appreciate both of your input
    – BeerusDev
    Mar 10, 2022 at 13:44

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.