How to Correct Time Values in SharePoint

 Correcting the time stamp in a External Content types

Let’s say you just created an External Content Type in SharePoint and succesfully created a list in SharePoint Designer.
Below is a view of the data, the first column is ID, second column is startDate and third column is endDate.

Open up SQL Studio Manager, run your select statement :

The dateTime field on the second and third column in SQL table is not the same as the ECT in SharePoint!  Its’s off by 5 hours. Obviously something is wrong when the data is rendered on the page.  When creating an external list using Designer, DateTime fields are defaulted to UTC  (Coordinated Universal Time – which current  database system timestamp)  . The external  list has an associated XML file that uses UTC time within the TypeDescriptor for the DateTime field.

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

 

SharePoint changes the value of the time to UTC, thereby offseting the hours by 5.  The offset  would also change by one hour after DayLight savings time.  So if your SQL data is correct and it uses time values in local time, then the datetime values will be wrong in your external list.

 

Resolution:

1)      In SharePoint Designer, navgiate to External Content Types from Site Objects list
2)      Select  the External Content Type
3)      Click ‘Export BDC Model’
4)      Export the file your local computer.  It contains a .bdcm extension
5)      Rename the file using .XML extension
6)      Open file to edit
7)      Search for each LobDateTimeMode and change “UTC” to “Local”

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

8)      Save file on local computer. Rename back to .bdcm
9)      In Designer, delete the External Content Type
10)   Open SharePoint Central Administration
11)   Click Manage Service Applications under Application Management
12)   Click Business Data Connectivity Service once and then click Manage
13)   Click Import
14)   Import your modified .bdcm file by clicking browse button.
15)   Go back to Designer and click Refresh. You should see your External Content type appear again.
16)   Delete the old list with the incorrect DateTime values and recreate a new one.
17)   List should now have correct DateTime values.

 

Advertisements

Tagged: , ,

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: