Dynamic OPENQUERY with DATETIME criteria

Question

Can someone please explain to me what is wrong with the below expression. I believe that's I'm converting my date correctly using CONVERT 126 and that my quotes are escaped correctly using char 39 but I could be wrong.

T-SQL:

DECLARE @end2 DATETIME2 = GETDATE();

DECLARE @test2 nvarchar(200) = N'SELECT * FROM OPENQUERY(x, ' 
  + char(39) + 'SELECT OBJID FROM SALE WHERE MODIFIED >= ' 
  + CHAR(39) + CONVERT(nvarchar(24),@end2,126) 
  + char(39) + char(39) + ')';

PRINT @test2;

EXEC (@test2);

Print output:

select * from openquery(x, 'SELECT OBJID FROM SALE 
WHERE MODIFIED >= '2023-01-19T11:55:21.1233'')

Error:

Msg 102, Level 15, State 1
Incorrect syntax near '2023'.

Tried different formats, casting, etc. I can't use EXEC x..sys.sp_executesql because x is Firebird, not another SQL Server.

Answer 1

You can escape the ' character with another one, i.e. ''. But you need to double escape it, i.e. your final string needs to have double single quotes in to be escaped in your dynamic SQL, which means a lot of escaping, i.e.

DECLARE @end2 DATETIME2
set @end2 = getdate()
declare @test2 nvarchar(200)
set @test2 = 'select * from openquery(x, ''SELECT OBJID FROM SALE WHERE MODIFIED >= '''''+convert(nvarchar(24),@end2,126)+''''''')'
print @test2
exec (@test2)

Which results in:

select * 
from openquery(x, 'SELECT OBJID FROM SALE WHERE MODIFIED >= ''2023-01-19T18:06:22.6033''')