Watch out with calculated DateTime fields in CSOM

25 April 2017 Robrecht-Van-Caenegem SharePoint Development 
var localDateOnlyValue = ctx.Web.RegionalSettings.TimeZone.UTCToLocalTime((DateTime)item.FieldValues["DateOnly"]);
ctx.ExecuteQuery();

I recently came across a baffling issue when using CSOM to connect to a SharePoint Online list to retrieve values from a Date and Time field and another field calculated based on the first one.
To illustrate the issue, I created a dummy list with 2 colums:

  • DateOnly: Date and Time (show Date Only)
    01
  • CalculatedDateOnly: Calculated with formula [DateOnly] + 1 (Also showing as Date Only)
    02

 

When creating new items in this list, all looks well. The Date Only and calculations are done correctly.

04

The story in CSOM

Now, up to CSOM, when accessing this list with the following code:

var list = ctx.Web.Lists.GetByTitle("TestDateTime");
var item = list.GetItemById(1);
ctx.Load(item);
ctx.ExecuteQuery();

var dateOnlyValue = item.FieldValues["DateOnly"];
var calculatedDataOnlyValue = item.FieldValues["CalculatedDateOnly"];

 

The values in the dateOnlyValue and calculatedDataOnlyValue are not what you might expect:

05

Observe that the DateOnlyValue is –2 hours from what was originally selected. Now this is perfectly explainable. SharePoint stores the DateTime values in UTC, and when using the web interface, SharePoint takes into account the regional settings of your site to convert between local and UTC. If you use CSOM however, you get the value in UTC, no problem here. However, my calculated field, that just did a +1 (add a day) is not returned in UTC, which is confusing, because i just said that CSOM would return datetime in UTC.

Assuming that the dateOnlyValue is UTC, you can convert this to local time as follows (the inverse also exists):

var localDateOnlyValue = ctx.Web.RegionalSettings.TimeZone.UTCToLocalTime((DateTime)item.FieldValues["DateOnly"]);
ctx.ExecuteQuery();


Now, imagine you need to use these field values to forward to the user or some business process, then you might need logic to convert UTC values to local (for the dateOnlyValue) and other logic that does nothing for the calculated field (calculatedDateOnly). So basicaly, you have to assume that DateTime fields will be UTC and calculated ones will not be UTC. Let’s make that assumption …

The story in REST

Remember the assumption from CSOM: DateTime fields will be UTC and calculated ones will not be UTC.

Let’s see if this assumption stays valid when making REST calls. The following REST call was used to retrieve the values:

/_api/lists/getbytitle('TestDateTime')/Items(1)?$select=DateOnly,CalculatedDateOnly

and the result shows the following (baffling result)

<content type="application/xml">
	<m:properties>
		<d:DateOnly m:type="Edm.DateTime">2017-04-24T22:00:00Z</d:DateOnly> 
		<d:CalculatedDateOnly>2017-04-25T22:00:00Z</d:CalculatedDateOnly> 
	</m:properties>
</content>

Now both are in UTC!!!!!

So I will have to change my assumption to: DateTime fields will be UTC, but in CSOM calculated DateTime fields are local.

Solution

Now what about a solution that works in CSOM? If your goal is to use the values from SharePoint as they are “shown” to the users. Then you’re better off using the property FieldValuesAsText on a listitem. 

var list = ctx.Web.Lists.GetByTitle("TestDateTime");
var item = list.GetItemById(1);
ctx.Load(item, it => it.FieldValuesAsText);
ctx.ExecuteQuery();

var dateOnlyValue = item.FieldValuesAsText["DateOnly"];
var calculatedDataOnlyValue = item.FieldValuesAsText["CalculatedDateOnly"];

 

Then both variables will contain the date values as shown in the web interface:

07

Tags :