Restrict Implicit Binary Conversion Mode for Queries

The restrict implicit binary conversion mode (CONVERSION_MODE set to 1) applies to all aspects of queries in general.

Comparison Operators

When CONVERSION_MODE = 1, the restriction applies to these operators:
  • =, !=, <, <=, >=, <>, !>, !<
  • BETWEEN … AND
  • IN
used in a search condition for these clauses:
  • WHERE clause
  • HAVING clause
  • CHECK clause
  • ON phrase in a join
  • IF CASE expression

Example

This query:

SELECT COUNT(*) FROM T1
WHERE cvb IN (SELECT csi FROM T2)

fails with the message:

"Invalid data type comparison in predicate 
(t1.cvb IN (SELECT t1.csi ...)), [-1001013]
['QFA13']"

String Functions

When CONVERSION_MODE = 1, the restriction applies to these string functions:
  • CHAR
  • CHAR_LENGTH
  • DIFFERENCE
  • LCASE
  • LEFT
  • LOWER
  • LTRIM
  • PATINDEX
  • RIGHT
  • RTRIM
  • SIMILAR
  • SORTKEY
  • SOUNDEX
  • SPACE
  • STR
  • TRIM
  • UCASE
  • UPPER

Example

This query:

SELECT ASCII(cvb) FROM t1 WHERE c1=1

fails with the message:

"Data exception - data type conversion is not
possible. Argument to ASCII must be string,
[-1009145] ['QFA2E']"
The following functions allow either a string argument or a binary argument. When CONVERSION_MODE = 1, the restriction applies to mixed type arguments, that is, one argument is string and the other argument is binary.
  • INSERTSTR
  • LOCATE
  • REPLACE
  • STRING
  • STUFF

Example

This query:

SELECT STRING(cvb, cvc) FROM t1 WHERE c1=1
where the column cvb is defined as VARBINARY and the column cvc is defined as VARCHAR, fails.

with the message:

"Data exception - data type conversion is not
possible. Arguments to STRING must be all binary 
or all string, [-1009145] ['QFA2E']"
The restriction does not apply to these string functions:
  • BIT_LENGTH
  • BYTE_LENGTH
  • CHARINDEX
  • LENGTH
  • OCTET_LENGTH
  • REPEAT
  • REPLICATE
  • SUBSTRING

Arithmetic Operations and Functions

When CONVERSION_MODE = 1, the restriction applies to these operators used in arithmetic operations:

+, -, *, /

The restriction applies to these bitwise operators used in bitwise expressions:

& (AND), | (OR), ^ (XOR)

The restriction also applies to integer arguments of these functions:
  • ROUND
  • TRUNCATE
  • TRUNCNUM

Example

This query:

SELECT ROUND(4.4, cvb) FROM t1 WHERE C1=1

fails with the message:

"Data exception - data type conversion is not
possible. Second Argument to ROUND cannot be
converted into an integer, [-1009145] ['QFA2E']"

Integer Argument to Various Functions

When CONVERSION_MODE = 1, the restriction applies to integer argument of these functions:
  • ARGN
  • SUBSTRING
  • DATEADD
  • YMD

Example

This query:

SELECT ARGN(cvb, csi, cti) FROM t1 WHERE c1=1

fails with the message:

"Data exception - data type conversion is not
possible. First Argument to ARGN cannot be converted
to an integer, [-1009145] ['QFA2E']"

Analytical Functions, Aggregate Functions, and Numeric Functions

When CONVERSION_MODE = 1, no further restriction applies to analytical functions, aggregate functions, and numeric functions that require numeric expressions as arguments.