0

I try to send a DATETIME variable within a linked server query on a Postgres server within a SQL Server environment.

My code:

DECLARE @start_date DATETIME;
SET @start_date ='2019-02-01';

SELECT *
FROM OPENQUERY (postgresDB, 'SELECT id, action_time
                             FROM call_history
                             WHERE action_time > ''' + @start_date + ''' ')

I tried to experiment with different numbers of quotation marks around the variable, but I always get this error:

Incorrect syntax near '+'

transit-samuelgrondin
  • 43
  • 1
  • 5
Xiaoyu Lu
  • 3,280
  • 1
  • 22
  • 34
  • 1
    `OPENQUERY` accepts literals only, not expressions and not variables. If possible, consider using `EXECUTE .. AT`, which not only accepts variables, but also parameters, so you don't need to rely on converting the `DATETIME` to a string value (a potential source of problems). If that's not an option because you need to process the results in ways `EXEC` doesn't allow for, you're stuck with first building the string and executing the whole batch containing `OPENQUERY` dynamically. – Jeroen Mostert Feb 04 '19 at 15:54
  • @JeroenMostert Thanks for the response. The original idea was to select the openquery into a temp table. As far as I understand, I will have to go with the second approach and build the whole batch first? – Xiaoyu Lu Feb 04 '19 at 15:56
  • `INSERT #t EXECUTE () AT ` is actually supported (at least it is for linking to other SQL Servers), but there may be consequences with distributed transactions, and you need to make sure your table's structure conforms exactly to what the query produces. – Jeroen Mostert Feb 04 '19 at 16:00

1 Answers1

0

Either convert your datetime into a string for the concatenating, or declare @startdate as an nvarchar or varchar.

When I try to select your string:

DECLARE @start_date datetime;
SET @start_date ='2019-02-01';

select 'SELECT
                    id,
                    action_time
                FROM call_history
                WHERE action_time > ''' + @start_date + '''
        '

I get:

Msg 241, Level 16, State 1, Line 4
Conversion failed when converting date and/or time from character string.

But this works fine:

DECLARE @start_date nvarchar(10);
SET @start_date ='2019-02-01';

select 'SELECT
                    id,
                    action_time
                FROM call_history
                WHERE action_time > ''' + @start_date + '''
        '
Ben Kean
  • 182
  • 1
  • 10
  • Unfortunately none of this will help addressing the fact that `OPENQUERY` does not support expressions one way or another, even if the concatenation expression was fixed. – Jeroen Mostert Feb 04 '19 at 16:01