Trying to set date field to midnight SharePoint regional time

[eshenradgen]

I have a SharePoint app where the user can enter a date and update the date field in a SharePoint list item.   I want to store the time of the date field to be midnight SharePoint regional time.

I can easily shift the hours if SharePoint and the user's computer are in different time zones.  The trouble I'm having is when the user's computer time zone has different Daylight Savings Time rules as SharePoint (e.g.. SharePoint regional time zone is in the United States and the user's computer is in Europe which has a different DST start/stop dates).  

I know that I can do an API call to convert UTC time to SharePoint regional time but that would be quite cumbersome if I'm updating hundreds of items.  Is there any way to determine when SharePoint's regional time zone is observing Daylight Savings Time without having to do a lot of API calls?

[#2]

Hi @eshenradgen,

you don't need to do all of that.

Just convert your timestamp in your app from local time to UTC time. Then use the UTC timestamp in the REST api call

{
   "MyDateField":"2023-03-02T00:00:00Z"
}

The "Z" at the end marks it as an UTC (or Zulu) timestamp.

In Javascript you can use the "toISOString" Method to get that 

new Date().toISOString()

 

Best Regards,

Sven

[#3]

Thanks for the reply but I want to set the time to midnight of the SharePoint's regional setting's time zone. For example, if SharePoint's regional setting is Pacific (-8 UTC), I want to have the date to be set to 16:00 UTC time so it appears in SharePoint as midnight Pacific.

I am able to find what time zone the SharePoint regional setting is set to but I don't have an easy way to know whether the date is during daylight savings time. North America, Europe and South America have different rules for daylight savings time.

I'm beginning to think that an API call is the only way to go. I can't see any SharePoint objects having a function that is complex enough to calculate when daylight saving time starts and ends.

[#4]

@eshenradgen 

You set the timezone (including what rules for daylightsavings time to use) in the regional setting of your SharePoint site (That is why you have mulitple entries for some "UTC+Hour" values. They handle different  daylight savings time settings). 



If you set your site's regional settings to "Pacific" and then update a date using the rest api and don't provide any specific timezone information like this

{
   "Date":"2023-03-02T00:00:00"
}

that date is being interpreted as a "Pacific" time.

If you then right now (04.February) switch your regional settings to 

SharePoint displays that time as 20:00 hours.

But if you set your regional settings to 

SharePoint displays that time as 21:00 hours. That is because New Zealand has daylight savings time right now.

If you now update the date in Auckland time using the REST api like this

{
   "Date":"2023-03-02T00:00:00"
}

and switch the site back to "Pacific" time then you get the time of 03:00.


So, if you want to set a date to the midnight local timezone of your SharePoint site including daylight savings time then just post it without any timezone information. SharePoint knows when daylight savings time starts and ends for a region.

If you want to set a date to the midnight local timezone of your user's browser including daylight savings time, then fetch the user's local time at midnight and convert it to UTC and post that date. The user's browser will also take care of the  correct timezone and daylight savings time for you
So in that case just post the value of the expression

new Date(new Date().format("yyyy-dd-mmT00:00:00")).toISOString()

to SharePoint. The regional settings of the site don't matter in this case, as this is an UTC timestamp (with the "Z" at the end).

Best Regards,
Sven

[#5]

@SvenSieverding 

Thanks again for the reply.  I didn't know that time in dates in REST API calls can be treated as relative to SharePoint's regional time zone.   

I use JSOM (JavaScript Object Model) to update my SharePoint lists.  In order to update a Date/Time field, I would need to pass in a data object.  Unfortunately the date object is an absolute time so I am not able to tell SharePoint to treat it as relative to their regional time zone.

var context = SP.ClientContext.get_current();
// item is a SP.ListItem object
item.set_item('DateField', <date object>);
item.update();
context.executeQueryAsync();

[#6]

Hi @eshenradgen,

in that case you can just create a Javascript "Date" object and use it's ".toISOString()" method

var dateString=new Date("2023-07-02T00:00:00").toISOString();

Then you can insert a list item this

var clientContext = new SP.ClientContext(_spPageContextInfo.siteAbsoluteUrl);
var oList = clientContext.get_web().get_lists().getByTitle('test');
var itemCreateInfo = new SP.ListItemCreationInformation();
this.oListItem = oList.addItem(itemCreateInfo);

//Single line of text  
oListItem.set_item('Title', 'My New Item!');
//Format local date to UTC format 
var dateString=new Date("2023-07-02T00:00:00").toISOString();
oListItem.set_item('MyDate', dateString);

oListItem.update();
clientContext.load(oListItem);
clientContext.executeQueryAsync(
    function(){},
    function(){},
);  

Best Regards,
Sven

[#7]

@SvenSieverding 

I tried that and the date is still being as midnight of my computer's time zone. When I ran the code snippet you r provided with my computer set to Pacific Time Zone (UTC-8) and my SharePoint regional setting set to Central Time Zone (UTC-6), MyDate field shows up as 2:00AM on the SharePoint site.

I even removed the trailing Z from dateString and still got the same result.

var clientContext = new SP.ClientContext(_spPageContextInfo.siteAbsoluteUrl);
var oList = clientContext.get_web().get_lists().getByTitle('test');
var itemCreateInfo = new SP.ListItemCreationInformation();
this.oListItem = oList.addItem(itemCreateInfo);

//Single line of text  
oListItem.set_item('Title', 'My New Item!');
//Format local date to UTC format 
var dateString=new Date("2023-07-02T00:00:00").toISOString();
dateString = dateString.replace(/Z$/, "");
oListItem.set_item('MyDate', dateString);

oListItem.update();
clientContext.load(oListItem);
clientContext.executeQueryAsync(
    function(){},
    function(){},
);  

Are you able to get the correct result when your browser and SharePoint regional settings are in different time zones?

[#8 · Accepted solution]

Hi @eshenradgen,

Mhm... the default Javascript date always assumes that you either enter the date in your local timezone or as UTC... Then you can translate it into different timezones... 
But you cannot parse a date from a different timzone using Javscript dates 

But more complex date calculations work great with moment.js and moment-timezone.js

If you include these libraries

<script src="https://cdnjs.cloudflare.com/ajax/libs/moment.js/2.29.4/moment.min.js"></script>
<script src="https://cdnjs.cloudflare.com/ajax/libs/moment-timezone/0.5.40/moment-timezone.min.js"></script>

you can use moment to create UTC timestamps for midnight in every timezone

//Central
var dateString=moment.tz("2023-07-02T00:00", "America/Chicago").utc().format()
// dateString is 2023-07-02T05:00:00Z

//Pacific
var dateString=moment.tz("2023-07-02T00:00", "America/Los_Angeles").utc().format()
// dateString is 2023-07-02T07:00:00Z

If you use "2023-07-02T05:00:00Z" with the former code (Or exchange line 6 from the lower code with line 2 from the upper)

var itemCreateInfo = new SP.ListItemCreationInformation();
this.oListItem = oList.addItem(itemCreateInfo);
//Single line of text  
oListItem.set_item('Title', 'Midnight Central');
//Format local date to UTC 
var dateString="2023-07-02T05:00:00Z"
oListItem.set_item('MyDate', dateString);
oListItem.update();
clientContext.load(oListItem);
clientContext.executeQueryAsync(
    function(){},
    function(){},
); 

you create an item with a timestamp at midnight on a SharePoint site set to CST.

From every timezone.

Best Regards,
Sven

PS, but just for fun:

If you now apply the following JSON formatting to that date column

  {  
    "$schema": "https://developer.microsoft.com/json-schemas/sp/v2/column-formatting.schema.json",  
    "elmType": "div",  
    "txtContent": {  
      "operator": "toLocaleString()",  
      "operands": [  
        "@currentField"  
      ]  
    }  
  }  

then the time will be still stored in the timezone of the site, but will be displayed in the browser's local timezone. So it would display a a "Pacific" time, even if the site is set to CST.