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...?

If ysnPending is nullable then you can't NOT it. NOT NULL is NULL — Mitch Wheat
– Mitch Wheat, Commented Mar 6, 2010 at 6:07

Mitch Wheat · Accepted Answer · 2010-03-06 06:04:24Z

3

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?

answered Mar 6, 2010 at 6:04

Mitch Wheat

302k44 gold badges482 silver badges552 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Vikas Over a year ago

SELECT otherColumns, CASE WHEN ysnPending = NULL THEN 1 WHEN ysnPending = 0 THEN 1 WHEN ysnPending = 1 THEN 0 End FROM table worked for me!

Mitch Wheat Over a year ago

NULL maps to True? OK if that's what you need.

Moe Sisko Over a year ago

@Vikas - you probably want to use : "WHEN ysnPending IS NULL" instead of "WHEN ysnPending = NULL".

Moe Sisko · Accepted Answer · 2010-03-08 02:41:33Z

0

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).

answered Mar 8, 2010 at 2:41

Moe Sisko

12.2k9 gold badges52 silver badges88 bronze badges

Collectives™ on Stack Overflow

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

2 Answers 2

3 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related