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