Forum Discussion
eshenradgen
Feb 02, 2023Copper Contributor
Trying to set date field to midnight SharePoint regional time
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. ...
- Feb 07, 2023
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.
eshenradgen
Feb 03, 2023Copper Contributor
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.
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.
SvenSieverding
Feb 04, 2023Bronze Contributor
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
- eshenradgenFeb 07, 2023Copper Contributor
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();
- SvenSieverdingFeb 07, 2023Bronze Contributor
Hi eshenradgen,
in that case you can just create a Javascript "Date" object and use it's ".toISOString()" methodvar dateString=new Date("2023-07-02T00:00:00").toISOString();
Then you can insert a list item thisvar 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- eshenradgenFeb 07, 2023Copper Contributor
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?