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.
Name=”DueDate” DefaultDisplayName=”Start Date”>
<NormalizeDateTime LobDateTimeMode=”UTC” />
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.
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”
Name=”DueDate” DefaultDisplayName=”Due Date”>
<NormalizeDateTime LobDateTimeMode=”Local” />
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.