4

I have an external content type in SharePoint 2010 pulling data from a SQL 2008R2 DB using BCS. All the data comes back fine but the Time stamp column is off by 2 hours when I compare the values displayed in the Read List with the values returned when I query the DB in Management Studio. I've attached a picture with an example:

Example

The times displayed in the browser are 4 hours earlier than the Time stamp returned by Management Studio. I've checked the Date and Time settings on both the client and all the servers and they are fine. Any ideas what could cause this?

  • Could we know the Timezones that each of the servers is in ? – Cameron Verhelst Feb 25 '14 at 20:09
  • Time zone is UTC -04:00 – HasaniH Feb 25 '14 at 20:22
4

This article seems to agree on my earlier comment, the interesting bit being:

When you create an External Content Type and External List using the SharePoint Designer, DateTime fields are normalized to UTC using the NormalizeDateTime setting associated with the TypeDescriptor for the DateTime field.

the fix being:

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

which you should add (or configure in SPD) to your bcs model.

You do that by:

  1. Open the 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. Open the File in Notepad or Visual Studio
  7. Locate the TypeDescriptor for the DateTime field
  8. Modify the element as shown above
  9. Save file on Desktop
  10. Delete the ECT from SharePoint Designer
  11. Close SharePoint Designer
  12. Go to Central Administration site
  13. Click Manage Service Applications
  14. Click Business Data Connectity Service
  15. Click Import to import the modified file
  • This seemed promising but it did not resolve my issue, I made the change to model but I still see the discrepancy – HasaniH Feb 27 '14 at 14:59
  • It must be related somehow, as your discrepancy is indeed the timezone difference. I saw other people reported that ot didn't work for them but I'd still check to see what SharePoint / SQL server consider the local timezone – Cameron Verhelst Feb 27 '14 at 19:42
  • I messed it up on the first attempt but this did do the trick – HasaniH Mar 12 '14 at 21:47
  • The links are now dead. How does one get to edit the BSC model in SPD to add the required tags? – Aaron Mar 1 '17 at 17:33

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.