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.