How to pass date variable in open query?

Question

I try to pass date variable in open query but it shows error like

"Incorrect syntax near + "

Here is my query:

DECLARE @fromdt DATETIME = '2018-04-07';
 DECLARE @EndDate1 DATETIME = '2018-04-07';
    Select * from openquery(TIMEV,
       'SELECT REPLACE(LTRIM(REPLACE(badgenumber,"0"," "))," ","0") badgenumber,
checktime as dt
from checkinout a 
join USERINFO c on c.userid=a.userid  
        WHERE checktime >= '''''+CONVERT(CHAR(10), @fromdt, 120)+'''''  AND ''''' + CONVERT(CHAR(10), @EndDate1, 120) + ''''' ')

I am stuck here .Thanks in advance..

Have you tried manipulating the date parameters in a separate command? You should be able to pass the parameters in rather than using dynamic sql — LJ01
– LJ01, Commented Apr 8, 2018 at 6:04
@LJ01 - this wouldn't help because the OPENQUERY documentation explicitly forbids using any variables. So it's not a problem of converting, it's a problem of any variable being passed to it. Should be remedied, though, with something like in my answer. The provided link has multiple workarounds for this. — MK_
– MK_, Commented Apr 8, 2018 at 7:10
Sorry, I'm still not seeing any point in using openquery at all. Is there something in there that can't be done using a standard query? — LJ01
– LJ01, Commented Apr 8, 2018 at 8:02

MK_ · Accepted Answer · 2018-04-10 07:20:06Z

2

OPENQUERY documentation explicitly says:

OPENQUERY does not accept variables for its arguments.

Therefore, using a trick from this site you should be able to do it like this:

DECLARE @fromdt DATETIME = '2018-04-07';
DECLARE @EndDate1 DATETIME = '2018-04-07';

DECLARE @query nvarchar(max) = 
'SELECT * FROM OPENQUERY(TIMEV,
    '' SELECT REPLACE(LTRIM(REPLACE(badgenumber,"0"," "))," ","0") badgenumber,
       checktime AS dt
       FROM checkinout a 
         JOIN USERINFO c on c.userid=a.userid  
       WHERE checktime >= ''''' + CONVERT(CHAR(10), @fromdt, 120) + '''''  AND ''''' + CONVERT(CHAR(10), @EndDate1, 120) + ''''' ''
)';

EXEC(@query);

EDIT: there are also two other methods suggested in the link I provided above the code snippet if you wish to try them out.

edited Apr 10, 2018 at 7:20

answered Apr 8, 2018 at 7:06

MK_

1,1797 silver badges18 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

dakab Over a year ago

I successfully adapted this approach (but using 121 as the CONVERT style).

Collectives™ on Stack Overflow

How to pass date variable in open query?

1 Answer 1

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related