How to alter the boolean value in SQL Server select query?

Question

Basically I want to alter the boolean value selecting from the table:

e.g.:

SELECT otherColumns, not ysnPending FROM table

I need a column ysnPending = true if the value is false & false if the value is true.

Is there any function available to alter the Boolean value or I should use IIf or CASE...?

Answer 1

use CASE, or if the bit field is non-nullable you could just subtract from 1.

SELECT 
    otherColumns, 
    (1 - ysnPending) -- NOT ysnPending
FROM table 

(Using CASE might lead to more understandable code.)

If ysnPending is nullable, what behaviour do you assign to NOT?

Answer 2

Example using a case statement :

create table table1 (id int not null, ysnPending bit null)
insert table1 values (1, 1)
insert table1 values (2, null)
insert table1 values (3, 0)

select id, cast((case when ysnPending = 1 then 0 else 1 end) as bit) as Not_ysnPending from table1

Assumes you want 1 returned when ysnPending is NULL.

The cast to bit type is to make sure that the returned column is of a BIT datatype. If you leave it out, it will return an INTEGER type. (This may or may not matter to you, depending on how exactly you are going to use the returned result set).