Issue:
Had a issue raised by one of our clients that while adding holidays to an external list the list was changing the values. For example, New Year day – Even after entering the date as 1/1/2013 12:00 am. It was changing it to 12/31/2013 6 pm.
Cause:
This was caused due to the Normalization Settings. When external content type is created using SharePoint Designer, DateTime fields are set to UTC using the NormalDateTime setting associated to TypeDescriptor for that particular field.
In my scenario, it was displaying as –
<TypeDescriptor TypeName=”System.DateTime” CreatorField=”true” Name=”HolidayDate”>
<Interpretation>
<NormalizeDateTime LobDateTimeMode=”UTC” />
</Interpretation>
</TypeDescriptor>
To fix the issue this needs to read as
<TypeDescriptor TypeName=”System.DateTime” CreatorField=”true” Name=”HolidayDate”>
<Interpretation>
<NormalizeDateTime LobDateTimeMode=”Local” />
</Interpretation>
</TypeDescriptor>
Resolution:
Follow these steps to fix this issue –
1. Open SharePoint Designer.
2. Open the Site.
3. Select External Content types under Site Objects.
4. Select the External Content Type. In my scenario Holidays. Do not click on the name. Just select the row.
5. On the Ribbon click on Export BDC Model.
6. Save the BDC Model to your local machine.
7. The extension would be – BDCM. Change this extension to xml.
8. Open the file in notepad.
9. Locate all the TypeDescriptor for your datetime fields. This is important step. We have to replace all the instances of the TypeDescriptor datetime field. Make sure the Typedescriptor’s interpration element matches the following format –
<TypeDescriptor TypeName=”System.DateTime” CreatorField=”true” Name=”HolidayDate”>
<Interpretation>
<NormalizeDateTime LobDateTimeMode=”Local” />
</Interpretation>
</TypeDescriptor>
10. Save the file.
11. Delete the External Content Type from SharePoint Designer.
12. Close SharePoint Designer.
13. Go to Central Administration
14. Click on Manage Service Applications under Application Management
15. Click on your Business Data Connectivity service.
16. On the ribbon click Import BDC Models.
17. On Import BDC Model browse and select the file.
18. Keep the remaining options as is.
19. Click Import.
20. If everything worked then it will give you import successful message.
21. Make sure you set the object permissions on the newly created External Content Type.
22. Go to your site and delete the external list created for the content type.
23. Recreate this list.
That is it !!!! Your date time should be displaying the correct format.
Ameet Phadnis Technical Blog 