Search code examples
sql-serversalesforcesoql

SOQL - Convert Date To Owner Locale

We use the DBAmp for integrating Salesforce.com with SQL Server (which basically adds a linked server), and are running queries against our SF data using OPENQUERY.

I'm trying to do some reporting against opportunities and want to return the created date of the opportunity in the opportunity owners local date time (i.e. the date time the user will see in salesforce).

Our dbamp configuration forces the dates to be UTC.

I stumbled across a date function (in the Salesforce documentation) that I thought might be some help, but I get an error when I try an use it so can't prove it, below is the example useage for the convertTimezone function:

SELECT HOUR_IN_DAY(convertTimezone(CreatedDate)), SUM(Amount)
FROM Opportunity
GROUP BY HOUR_IN_DAY(convertTimezone(CreatedDate))

Below is the error returned:

OLE DB provider "DBAmp.DBAmp" for linked server "SALESFORCE" returned message "Error 13005 : Error translating SQL statement: line 1:37: expecting "from", found '('".
Msg 7350, Level 16, State 2, Line 1
Cannot get the column information from OLE DB provider "DBAmp.DBAmp" for linked server "SALESFORCE".

Can you not use SOQL functions in OPENQUERY as below?

SELECT
    *
FROM
    OPENQUERY(SALESFORCE,'
        SELECT HOUR_IN_DAY(convertTimezone(CreatedDate)), SUM(Amount) 
        FROM Opportunity 
        GROUP BY HOUR_IN_DAY(convertTimezone(CreatedDate))')

UPDATE:

I've just had some correspondence with Bill Emerson (I believe he is the creator of the DBAmp Integration Tool):

You should be able to use SOQL functions so I am not sure why you are getting the parsing failure. I'll setup a test case and report back.

I'll update the post again when I hear back. Thanks

Solution

  • A new version of DBAmp (2.14.4) has just been released that fixes the issue with using ConvertTimezone in openquery.

    Version 2.14.4

    • Code modified for better memory utilization
    • Added support for API 24.0 (SPRING 12)
    • Fixed issue with embedded question marks in string literals
    • Fixed issue with using ConvertTimezone in openquery
    • Fixed issue with "Invalid Numeric" when using aggregate functions in openquery