0

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?

Jonathan Leffler
  • 730,956
  • 141
  • 904
  • 1,278
cococolada
  • 13
  • 2
  • If the intent is to concatenate @startDate in the middle of the string before passing to Openquery, then you need three quotes in a row before and after `+`, not two. – Fred Jan 27 '21 at 21:03
  • @Fred When I tried that I get the following error: Incorrect syntax near '+'. – cococolada Jan 27 '21 at 21:26
  • Why declare @startDate as a DATETIME? In order to concatenate you would need to convert it to VARCHAR anyway. – Fred Jan 27 '21 at 22:40
  • Also, based on your example code this is a `sql-server` question, though the original error message was from `informix` because the value passed was literally `'+@startDate+'` – Fred Jan 27 '21 at 22:53
  • @Fred Thanks for your help. It was an informix issue. – cococolada Jan 29 '21 at 21:08

1 Answers1

0

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.

Jonathan Leffler
  • 730,956
  • 141
  • 904
  • 1,278