none
Retrieving DateTime and Calculated fields in CSOM gives strange behavior RRS feed

  • Question

  • Hi,

    I have the following setup:

    I have a list with 2 columns:

    • DateOnly: a column that is Date and Time (but showing Date Only)

    • CalculatedDateOnly: a column that is Calculated as [DateOnly] + 1 (also showing as Date Only)

    I create a new item in this list using the web interface, and the columns look to be working correctly.

    Now it's up to CSOM to retrieve those values using 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"];

    Now the values in those fields are not really what I would expect, because I get the following:

    This is strange, the dateOnlyValue illustrates a value 2 hours before the one I have chosen, but I can perfectly relate to the fact that SharePoint stores dates in UTC, and my regional settings of my site will introduce this 2 hour difference.

    What is strange is that the calculatedDataOnlyValue does not give me this 2 hour difference, why not? Because it is a calculated field? And I have to "assume" that calculated date fields are not UTC?

    Then why not try to use REST instead of CSOM, to see if the result is similar. I did the following REST call:

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

    And this shows the following values for those fields:

    <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>

    Both fields with a 2 hour difference, so both in UTC...

    Is there a way to make sure that CSOM does it correctly, i.e.:

    • Both dates are returned in UTC
    • Both dates are returned how they were filled in/calculated
    Tuesday, April 25, 2017 8:39 AM

Answers

  • Hi Lee, 

    It's even better to use CSOM to convert to local or UTC, because if I would use 

    System.TimeZone.CurrentTimeZone.ToLocalTime(dateOnlyValue);

    then the time zone of my machine/server is considered as the local one, and not the one in the Regional Settings of the site. Therefore I am using CSOM

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

    The problem however is that the DateOnly field is returned in UTC, and the calculated one is not.
    I found a partial solution to this, by checking the Kind property of a DateTime, this one will tell if it is UTC, local or something else. And then use it as follows

    if(calculatedDataOnlyValue.Kind == DateTimeKind.Utc)
    {
      // Convert to local
      var localCalculatedDataOnlyValue = ctx.Web.RegionalSettings.TimeZone.UTCToLocalTime(calculatedDataOnlyValue);
      ctx.ExecuteQuery();
    
      // Continue with the local one ...
    }
    else if(calculatedDataOnlyValue.Kind == DateTimeKind.Local)
    {
      // All is good ...
    }
    

    You can then observe that the DateOnly is UTC, and the calculated is Local. However, this is a partial solution, because when benchmarking this code, I bumped into cases where the calculated claimed to be Local, but the value in it was really UTC...

    • Marked as answer by Robrecht [U2U] Thursday, April 27, 2017 10:43 AM
    Thursday, April 27, 2017 10:43 AM

All replies

  • Hi,

    You could try this:

    System.TimeZone.CurrentTimeZone.ToLocalTime(dateOnlyValue);

    Here is the thread for your reference.

    https://reshmeeauckloo.wordpress.com/2015/11/19/convert-utc-date-returned-from-sharepoint-api-to-local-time-using-javascript-and-c/

    Here is my test result.

    Best Regards,

    Lee


    Please remember to mark the replies as answers if they help.
    If you have feedback for TechNet Subscriber Support, contact tnmff@microsoft.com

    Thursday, April 27, 2017 9:58 AM
  • Hi Lee, 

    It's even better to use CSOM to convert to local or UTC, because if I would use 

    System.TimeZone.CurrentTimeZone.ToLocalTime(dateOnlyValue);

    then the time zone of my machine/server is considered as the local one, and not the one in the Regional Settings of the site. Therefore I am using CSOM

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

    The problem however is that the DateOnly field is returned in UTC, and the calculated one is not.
    I found a partial solution to this, by checking the Kind property of a DateTime, this one will tell if it is UTC, local or something else. And then use it as follows

    if(calculatedDataOnlyValue.Kind == DateTimeKind.Utc)
    {
      // Convert to local
      var localCalculatedDataOnlyValue = ctx.Web.RegionalSettings.TimeZone.UTCToLocalTime(calculatedDataOnlyValue);
      ctx.ExecuteQuery();
    
      // Continue with the local one ...
    }
    else if(calculatedDataOnlyValue.Kind == DateTimeKind.Local)
    {
      // All is good ...
    }
    

    You can then observe that the DateOnly is UTC, and the calculated is Local. However, this is a partial solution, because when benchmarking this code, I bumped into cases where the calculated claimed to be Local, but the value in it was really UTC...

    • Marked as answer by Robrecht [U2U] Thursday, April 27, 2017 10:43 AM
    Thursday, April 27, 2017 10:43 AM
  • Hi Robrecht,

    Thanks for your sharing.

    Best Regards,

    Lee


    Please remember to mark the replies as answers if they help.
    If you have feedback for TechNet Subscriber Support, contact tnmff@microsoft.com

    Thursday, April 27, 2017 10:56 AM