Date Only SharePoint Column - Expected Behavior with Times

  • 16 February 2021
  • 0 replies
  • 387 views

Userlevel 5
Badge +20
 

Date Only SharePoint Column - Expected Behavior with Times

KB001739

PRODUCT
K2 blackpearl 4.6.10 to 4.6.11
K2 smartforms 4.6.10 to 4.6.11
BASED ON
K2 blackpearl 4.6.10
LEGACY/ARCHIVED CONTENT
This article has been archived, and/or refers to legacy products, components or features. The content in this article is offered "as is" and will no longer be updated. Archived content is provided for reference purposes only. This content does not infer that the product, component or feature is supported, or that the product, component or feature will continue to function as described herein.

 

Introduction

In some cases you may notice that your Date Only columns in SharePoint do not contain the expected date and time values. This happens mainly when using SharePoint-based SmartForms outside of the context of SharePoint, but can happen when interacting with SharePoint-based SmartObjects that contain a Date Only field from SharePoint.

 

 

Background Information

When specifying the Date Only format for a SharePoint column, as pictured below, the underlying date is stored with a time because SQL Server requires that a time be stored with all dates. 

Image

The time that gets stored for a Date Only-formatted column is midnight of the chosen day, so if November 30th is specified, the value stored by SQL is:

2016-11-30 00:00:00

However, if the SharePoint site is in a time zone offset of +3, the time stored by SharePoint becomes:

2016-11-29 09:00:00 PM

SharePoint takes into account what the time zone is of the site where the date is specified and adjusts it according to the offset. This means that a value of midnight on November 30th in a +3 UTC offset is 3 hours later than UTC, which means that the UTC date is adjusted by three hours earlier and becomes 9 PM on November 29, and that is what SharePoint stores. If you were to open that list or library containing that date on a SharePoint site that is in the US Pacific time zone (UTC -8), that date-only column would read 11/29/2016. If the format of the column were changed to show the time as well as the date, it would read 11/29/2016 01:00:00 PM, just after mid-day on the 29th. This is because the time is adjusted automatically by SharePoint. 

When you stay within the SharePoint environment, including if you use K2 integration with lists and libraries, the date is never shown with a time, which is expected. However, if your solution and people using the list or library span multiple time zones, the date may be shifted one way or another depending on the number of time zones, the time zone of the original entry, and the time zone of the site in which the entry is viewed.

Furthermore, since K2 handles culture-specific data differently and stores data using the regional settings of the K2 server, there is an additional adjustment that happens. When in SharePoint, SmartForms adjusts according to the regional settings of the site. However, if the SmartForm is opened outside of SharePoint, this adjustment does not happen. This is also true when calling the SmartObject directly, such as through the service tester tool or via the API. The adjustment, in this case, happens based on the regional settings of the browser (for SmartForms) or of the local machine (for the API and the service tester tool).

The important thing to keep in mind is that, while in SharePoint, SmartForms behave exactly as standard SharePoint lists and libraries do. However, when working with SmartObjects directly (or via SmartForms opened outside the context of SharePoint), and the SmartObject is based on a SharePoint list or library containing a Date Only-formatted column, you may see different dates than what you are expecting. Make sure you test these scenarios and make the adjustments required for your scenario, either in your custom tool or via SmartForms rules. You must use a UTC offset value to adjust your date & time values before saving items to a SharePoint-based SmartObject and when you retrieve those values.

 


0 replies

Be the first to reply!

Reply