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.