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)

Links

Todd Baginski's Blog
BCS Team Blog
SharePoint Team Blog
SharePoint Workspace Team Blog
Andrew Connell's Blog
Ted Pattison's Blog

Tag Cloud

Business Data Connectivity, Claims, Conferences, MOSS 2007 Archived Post, PowerShell, Search, SharePoint 2010,

Why are my date fields wrong in Business Connectivity Services? 

Tags: SharePoint 2010

When you create an External Content Type and External List using the SharePoint Designer, DateTime fields are normalized to UTC using the following XML 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.

 
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