Why doesn't comparison work in CONVERT?

Question

Can I use the comparison operator in the CONVERT or CAST function?

I have a statement that looks like this:

SELECT ... CASE field WHEN 'Y' THEN 1 # If Y then True ELSE 0 # Anything else is False END ... FROM ... 

A similar situation arises for several fields, so I would like to change it to a shorter version:

 SELECT ... CONVERT(BIT, field = 'Y') ... FROM ... 

But MSSQL gives the error Incorrect syntax near '='.

My interpretation of help is that it should work:

• CONVERT ( data_type [ ( length ) ] , expression [ , style ] )
• expression: expression { binary_operator } expression
• binary_operator : It is an operator that determines how two expressions are combined to produce a single result. binary_operator can be an arithmetic operator, an assignment operator (=), a bitwise operator, a comparison operator, a logical operator, a string concatenation operator (+), or a unary operator.
• comparison operator: ( = | > | < | >= | <= | <> | != | !< | !> )

I did some tests and got the following results:

 SELECT CONVERT(BIT, 0) // 0 SELECT CONVERT(BIT, 1) // 1 SELECT CONVERT(BIT, 1+2) // 1 SELECT CONVERT(BIT, 1=2) // Error SELECT CONVERT(BIT, (1=2)) // Error SELECT CONVERT(BIT, (1)=(2)) // Error 

Answer 1

I think you are misinterpreting the documentation for CONVERT . There is nothing in the documentation for CONVERT that states that it will process an expression that uses comparison operators, only that it accepts the expression. It turns out that CONVERT does not handle all valid SQL expressions. At the very least, it cannot process the results of an expression that uses the comparison operator.

If you check the documentation for "Operators" , you will see that the comparison operators (what you need = ), in this case) return the Boolean data type and are used in WHERE clauses and flow control operations. From the documentation for operators:

The result of the comparison operator is the Boolean data type, which has three values: TRUE, FALSE, and UNKNOWN. Expressions that return the Boolean data type are known as Boolean expressions.

Unlike other SQL Server data types, the Boolean data type cannot be specified as the column or table variable data type, and cannot be returned in the result set.

...

Expressions with Boolean data types are used in the WHERE clause to filter strings that match the search conditions, and language flow control instructions such as IF and WHILE ...

This helps explain why SQL, such as SELECT 1=2 , is invalid SQL because it will create a result set with the Boolean data type, which the documentation says is invalid. This also explains why the CASE WHEN clause is needed because it can evaluate comparison operators and return a single data type value that SQL Server can return in the result set.

In addition, if you look at the documentation for CONVERT , you will see that Boolean not supported in either CAST or CONVERT (see the table in the middle of the page, there is no Boolean data type).

For your purposes, I think you are stuck with CASE WHEN . If this helps you write everything on one line:

 CASE WHEN field = 'Y' THEN 1 ELSE 0 END 

Alternatively, you can create UDF to handle the CASE expression (something like dbo.func_DoCase(field, 'Y', 1, 0) ), but personally, I would just stick with CASE WHEN .