CODEWITHSUNDEEP
restazuresharepointazure-logic-apps
nyn3x
nyn3x

Reputation: 919

How to convert SharePoint Date-Only field from UTC to local time zone

I've created a logic-app which reads data from SharePoint online using the get-item action.

However - I have a sharepoint-column of type Date-Only, which returns obviously just the date-portion (as date-only would imply).

The problem is, that sharepoint internally uses date-time (UTC!) to store the date. So when I create an entry in sharepoint in my local time (CET!) with the date "2016-10-20" this is stored as 2016-10-19T22:00.00Z.

So now the logic-app-action is sending a rest-call, which will return 2016-10-19. So how am I supposed to get my original date? Even if I would treat the response as UTC and convert it to my local time (using an azure-function) I would get 2016-10-19T02:00.00 as the result instead of 2016-10-20T02:00.00

Upvotes: 4

Views: 15249

Answers (1)

Vadim Gremyachev
Vadim Gremyachev

Reputation: 59358

While creating or updating entries SharePoint converts (based on current Time Zone selected in site Regional Settings) and saves datetime values in Coordinated Universal Time (UTC). SharePoint Online REST service returns datetime in UTC (apart from another services in Office 365, like Outlook services where it could be controlled via Prefer: outlook.timezone header, see for example here for a more details)

Prerequisites

Moment.js library

Having said that you need to convert datetime value from REST response in order to display it in local time:

var orderDateString = "2016-10-19T22:00.00Z"; //relative datetime value in REST response 
var offset = 120; //CET offset
var localOrderDate = moment.utc(orderDateString).utcOffset(offset);
console.log(localOrderDate.format('LL')); //print local date part

Or you could utilize the following function which converts the specified DateTime value from Coordinated Universal Time (UTC) to local time

var TimeZoneUtilities = {
  utcToLocalTime: function getSPCurrentTime(webUrl,value) {
    return $.ajax({
      url: webUrl + "/_api/web/RegionalSettings/TimeZone",
      method: "GET",
      headers: { "Accept": "application/json; odata=verbose" }
    }).then(function (data) {
      var offset = -(data.d.Information.Bias + data.d.Information.StandardBias + data.d.Information.DaylightBias) / 60.0;
      return moment.utc(orderDateString).utcOffset(offset).toDate();
    });
  }
};

This function is similar to SPTimeZone.LocalTimeToUTC method from SharePoint API.

Usage

var orderDateString = "2016-10-19T22:00.00Z"; //UTC datetime value from REST response 
var orderDate = new Date(orderDateString);
TimeZoneUtilities.utcToLocalTime(_spPageContextInfo.webAbsoluteUrl,orderDate)
.done(function(value)
{
    console.log("Server time: " + value.format("yyyy MM dd"));
})
.fail(function(error){
    console.log(JSON.stringify(error));
});

Upvotes: 4

Related Questions