Using a variable in TO_DATE function

Question

I'm trying to return data from ORACLE via LINKED SERVER using todays date in the where clause

Code:

DECLARE @var_date char(10)
SET @var_date = (SELECT CONVERT(DATE,GETDATE()))

SELECT TOP(25) * FROM OPENQUERY (OPICS, 'SELECT 
CASE 
  WHEN OPICS.FXDH.BR = 10 THEN ''Botswana''
  WHEN OPICS.FXDH.BR = 20 THEN ''Mozanmbique''
  WHEN OPICS.FXDH.BR = 30 THEN ''Rwanada''
  WHEN OPICS.FXDH.BR = 40 THEN ''Tanzania''
  WHEN OPICS.FXDH.BR = 50 THEN ''Zambia''
  WHEN OPICS.FXDH.BR = 60 THEN ''Zimbabwe''
END AS COUNTRY,
OPICS.FXDH.DEALNO,
to_char (OPICS.FXDH.DEALDATE,''YYYY-MM-DD'') as Deal_Date,
to_char (OPICS.FXDH.VDATE, ''YYYY-MM-DD'') as V_Date,
OPICS.FXDH.PS, 
OPICS.FXDH.CCY, 
OPICS.FXDH.CCYAMT, 
OPICS.FXDH.CCYRATE_8,
OPICS.FXDH.CTRCCY, 
to_char (OPICS.OPER.OPERNAME) as OPERNAME,
to_char (OPICS.CUST.CFN1) as CFN1,
to_char (OPICS.PROD.PDESC) as PDESC,
to_char (OPICS.PORT.PORTDESC) as Report_Description
FROM OPICS.FXDH FXDH
INNER JOIN OPICS.OPER OPER ON (FXDH.BR = OPER.BR) AND (FXDH.TRAD = OPER.OPER)
INNER JOIN OPICS.CUST CUST ON (FXDH.CUST = CUST.CNO)
INNER JOIN OPICS.PROD PROD ON (FXDH.PRODCODE = PROD.PCODE)
INNER JOIN OPICS.PORT PORT ON (FXDH.PORT = PORT.PORTFOLIO) AND (FXDH.BR = PORT.BR)
 WHERE OPICS.FXDH.DEALDATE = TO_DATE(''2018-08-29'', ''YYYY-MM-DD'')  
    --dateString
AND OPICS.FXDH.BR = 10
--ORDER BY OPICS.FXDH.DEALDATE DESC');

The above code works fine and returns values

Is there a way I can use my Variable @var_date instead of hard copying in the date I need/want.

I have tried to use the following but get errors when using the code:

WHERE OPICS.FXDH.DEALDATE = TO_DATE(@var_date, ''YYYY-MM-DD'')
WHERE OPICS.FXDH.DEALDATE = CONVERT(DATETIME , @var_date, 120)

You'll need to use dynamic sql. OPENQUERY only accepts a literal string. — Thom A
– Thom A ♦, Commented Apr 1, 2019 at 9:33
For future reference; when you get errors, mention the error messages in your question. — HoneyBadger
– HoneyBadger, Commented Apr 1, 2019 at 9:37

Thom A · Accepted Answer · 2019-04-01 09:42:05Z

As I mentioned in my comment You'll need to use dynamic sql. OPENQUERY only accepts a literal string.

As a result you'll need to do something like this:

DECLARE @var_date date = GETDATE(); --Note the datatype change

DECLARE @SQL nvarchar(MAX);
SET @SQL = N'SELECT TOP(25) * FROM OPENQUERY (OPICS, ''SELECT CASE
            WHEN OPICS.FXDH.BR = 10 THEN ''Botswana''
            WHEN OPICS.FXDH.BR = 20 THEN ''Mozanmbique''
            WHEN OPICS.FXDH.BR = 30 THEN ''Rwanada''
            WHEN OPICS.FXDH.BR = 40 THEN ''Tanzania''
            WHEN OPICS.FXDH.BR = 50 THEN ''Zambia''
            WHEN OPICS.FXDH.BR = 60 THEN ''Zimbabwe''
       END AS COUNTRY,
       OPICS.FXDH.DEALNO,
       to_char(OPICS.FXDH.DEALDATE, ''YYYY-MM-DD'') AS Deal_Date,
       to_char(OPICS.FXDH.VDATE, ''YYYY-MM-DD'') AS V_Date,
       OPICS.FXDH.PS,
       OPICS.FXDH.CCY,
       OPICS.FXDH.CCYAMT,
       OPICS.FXDH.CCYRATE_8,
       OPICS.FXDH.CTRCCY,
       to_char(OPICS.OPER.OPERNAME) AS OPERNAME,
       to_char(OPICS.CUST.CFN1) AS CFN1,
       to_char(OPICS.PROD.PDESC) AS PDESC,
       to_char(OPICS.PORT.PORTDESC) AS Report_Description
FROM OPICS.FXDH AS FXDH
     INNER JOIN OPICS.OPER AS OPER ON (FXDH.BR = OPER.BR)
                                  AND (FXDH.TRAD = OPER.OPER)
     INNER JOIN OPICS.CUST AS CUST ON (FXDH.CUST = CUST.CNO)
     INNER JOIN OPICS.PROD AS PROD ON (FXDH.PRODCODE = PROD.PCODE)
     INNER JOIN OPICS.PORT AS PORT ON (FXDH.PORT = PORT.PORTFOLIO)
                                  AND (FXDH.BR = PORT.BR)
WHERE OPICS.FXDH.DEALDATE = ' + QUOTENAME(CONVERT(DATETIME , @var_date, 112),'''') + N'
  --dateString
  AND OPICS.FXDH.BR = 10;
--ORDER BY OPICS.FXDH.DEALDATE DESC'');';

--PRINT @SQL; --Your debugging best friend
EXEC sp_executesql @SQL;

Note I haven't tested this, but I assume Oracle accepts the ISO format for date string literals. If not, you may need to change the style type or apply some logic in the OPENQUERY to change it to something acceptable to Oracle.

Collectives™ on Stack Overflow

Using a variable in TO_DATE function

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related