MS SQL/OPENQUERY call to Informix -- too many single quotes? too few?

Question

I am trying to communicate with an external Informix datasource from an MS SQL 2016 database instance to issue a date bound query. To do this, I am doing the following:

--enter code here
declare @date_string varchar(10)
set @date_string = '08/01/2018'
-- this statement works
SELECT * FROM OPENQUERY ([ExternalLinkedServer], 'SELECT FIRST 10 * FROM informix.anydetailtable');
-- this does not work
SELECT * FROM OPENQUERY ([ExternalLinkedServer], 'SELECT FIRST 10 * FROM informix.anydetailtable WHERE eventdatetime between TODAY and date(' + @date_string + ')' );

---

EDITED (self-answer): After the reminder from David Dubois that variables are not accepted in OPENQUERY, here is the workaround approach:

enter code here
set @date_string = '''08/01/2018'''
declare @openquery nvarchar(4000), @TSQL nvarchar(4000), @LinkedServer nvarchar(4000)
SET @LinkedServer = '[ExternalLinkedServer]'
SET @OPENQUERY = 'SELECT * FROM OPENQUERY('+ @LinkedServer + ','''
SET @TSQL = 'SELECT count(*) FROM informix.anydetailtable WHERE eventdatetime between TODAY and date(' + @date_string + ')'')'
print @openquery+@tsql
EXEC (@OPENQUERY+@TSQL)

The output of the print @openquery+@tsql looks like this:

SELECT * FROM OPENQUERY([ExternalLinkedServer],'SELECT count(*) FROM informix.anydetailtable WHERE eventdatetime between TODAY and date('08/01/2018')')

This looks correct, but obviously it is not because I get a syntax error near '08'. I have spent way more time on this than I should! Any and all advice will be greatly appreciated!

Answer 1

The approach @David Dubois offered is correct, however, fuzzy for anyone looking for a concrete answer. This is what actually works. Remember to pay special attention to the myriad of single quotes in order to pass a literal value, as in the case of the date.

    declare @openquery nvarchar(4000), @TSQL nvarchar(4000), @LinkedServer nvarchar(4000)
    SET @LinkedServer = '[ExternalLinkedServer]'
    SET @OPENQUERY = 'SELECT * FROM OPENQUERY('+ @LinkedServer + ','''
    SET @TSQL = 'SELECT count(*) FROM informix.anydetailtable WHERE eventdatetime between ' + '''''2018-08-27 00:00:00''''' + '' + ' and TODAY' + ''')'
    -- print @openquery+@tsql   -- use this to examine your query, comment out when it is working
    EXEC (@OPENQUERY+@TSQL)