Unable to pass variable in Informix

Question

I was able to figure out how to get connected to Avaya CMS through Informix using SQL. The below query works but when I try to replace the ''1/01/19'' with a variable, I get the following error: "EIX000: (-1205) Invalid month in date"

Code that works

select * from Openquery(CMS, 'select * FROM dagent  WHERE ROW_DATE = ''1/01/19'' ');

Code that does not work

DECLARE @startDate DATETIME
SET @startDate = '2021-01-21'
select * from Openquery(CMS, 'select * FROM dagent WHERE ROW_DATE = ''+@startDate+'' ');

Does anyone have an idea what the problem could be?

Answer 1

The trouble is not enough single quotes.

You have:

'select * FROM dagent WHERE ROW_DATE = ''+@startDate+'' '
                                       ^^            ^^

In each case where you have two adjacent single quotes, you need a third too. The two single quotes map to one single quote, so the quoted string contains +@startDate+, not the concatenation of your variable.

You need:

'select * FROM dagent WHERE ROW_DATE = '''+@startDate+''' '

Now the first two single quotes in the triplet map to a single quote; the third terminates the string, the +@startDate+ becomes string concatenation, and then the next single quote starts a new string, the two single quotes map to one quote, and the space and single quote finish the string.

How to debug?

• Assign the string you used to a variable and print it.

• Assign the string I suggest to a variable and print it.