Tuesday, November 10, 2009

Working with Salesforce Datetimes via DBAmp

I came across some SQL today that was setting Salesforce Date/Time fields with UTC values using DBAmp, which shows Salesforce as a linked Server.

When the results were viewed in Salesforce the values were off by 8 hours when the user was set to the UTC time zone.
NOTE: I found it much easier to work with a Salesforce user where the Time Zone was set to "(GMT+00:00) Greenwich Mean Time (Europe/Dublin)". This avoided any extra conversion occuring in the Salesforce GUI.

It turns out by default (as clearly stated in the Installation Guide) that DBAmp will convert the UTC values from Salesforce to the "local timezone". This conversion is occuring for inserts as well as queries.

After setting the HKEY_LOCAL_MACHINE / Software / DBAmp/ NoTimeZoneConversion registry key to 1 and restarting SQL Server the issue went away.