RSS Feed Feed your read!

Scot Hillier is an independent consultant and Microsoft SharePoint Most Valuable Professional focused on creating solutions for Information Workers with SharePoint, Office, and related .NET technologies. A frequent speaker at TechEd and SharePoint Connections, he is also the author many books on Microsoft technologies including 5 for SharePoint 2010. Scot splits his time between consulting on SharePoint projects and training for Critical Path Training. Scot is a former U. S. Navy submarine officer and graduate of the Virginia Military Institute. Scot can be reached at scot@shillier.com

Archives

January 2010 (17)
February 2010 (1)
March 2010 (1)
April 2010 (2)
May 2010 (1)
June 2010 (1)
August 2010 (4)
September 2010 (6)
October 2010 (3)
November 2010 (2)
February 2011 (1)
March 2011 (4)
April 2011 (3)
May 2011 (1)
August 2011 (1)
March 2012 (1)
April 2012 (1)
July 2012 (3)
August 2012 (1)
September 2012 (1)
October 2012 (2)
November 2012 (3)
January 2013 (2)
February 2013 (3)
March 2013 (3)
April 2013 (3)
May 2013 (1)
September 2013 (2)
November 2013 (5)
January 2014 (2)
February 2014 (2)
March 2014 (1)
April 2014 (1)
May 2014 (1)
July 2014 (1)

Links

IT Unity
SharePoint Team Blog
Andrew Connell's Blog

Tag Cloud

Apps, Books, Business Data Connectivity, Claims, Conferences, JavaScript, jQuery, MOSS 2007 Archived Post, PowerShell, REST and CSOM, Search, SharePoint 2010, SharePoint 2013, Web Content Management,

Why are my date fields wrong in Business Connectivity Services? 

Tags: SharePoint 2010

There are three reasons why the dates displayed in the External List may be offset from the actual values in the source system.

  1. Time zone settings are incorrect
  2. Normalization settings are incorrect
  3. DateTime.Kind is incorrect 

Time Zone Settings

Values returned from External Systems are affected by the time zone settings of the SharePoint server, SQL server, web application, site collection, and user regional settings.

Check all of these settings to make sure that they are as expected.

 

Normalization Settings

When you create an External Content Type and External List using the SharePoint Designer, DateTime fields are normalized to UTC using the NormalizeDateTime setting associated with the TypeDescriptor for the DateTime field.

<TypeDescriptor TypeName="System.DateTime"
 Name="DueDate" DefaultDisplayName="Due Date">
 <Interpretation>
  <NormalizeDateTime LobDateTimeMode="UTC" />
 </Interpretation>
</TypeDescriptor>

This means that the time zone is normalized to UTC by applying an offset. For my East Coast, United States time zone in the summer, that subtracts 6 hours. If the field contains dates in the local time zone , then the dates will be incorrect when displayed in the External List. 

To fix the problem, you must export the BDC Metadata Model from SPD and change the normalization to use local time as follows:

<TypeDescriptor TypeName="System.DateTime"
 Name="DueDate" DefaultDisplayName="Due Date">
 <Interpretation>
  <NormalizeDateTime LobDateTimeMode="Local" />
 </Interpretation>
</TypeDescriptor>

Follow these steps to fix the model:

Export and Modify

1. Open Site in SharePoint Designer
2.  Select External Content Types from the Site Objects list
3. Find your ECT and select it
4. Click Export BDC Model
5. Export the file to your desktop
6. The file exports with a BDCM extension. Change the extension to XML.
7. Open the File in Notepad or Visual Studio
8. Locate the TypeDescriptor for the DateTime field
9. Modify the <Interpretation> element as shown above
10. Save file on Desktop
11. Delete the ECT from SPD
12. Close SPD

 Import

1. Go to Central Administration site
2. Click Manage Service Applications
3. Click Business Data Connectity Service
4. Click Import to import the modified file

 External List

Delete the existing list in SharePoint and recreate it. Now the dates should appear in the local time zone and format.

DateTime.Kind

When you send and receive dates from an External System, they are affected by the DateTime.Kind setting of the value in accordance with this article.

This means that you can have everything else right, but still see issues with the dates if you are sending a DateTime.Kind from your system other than that which the NormalizeDateTime setting expects.

 
Posted by Scot Hillier on 1-Sep-10
0 Comments  |  Trackback Url  |  Link to this post | Bookmark this post with:        
 

Links to this post

Comments