Boolean 'Not' in T-SQL Not Working on 'Bit' Datatype

Boolean 'NOT' in T-SQL not working on 'bit' datatype?

Use the ~ operator:

DECLARE @MyBoolean bit
SET @MyBoolean = 0
SET @MyBoolean = ~@MyBoolean
SELECT @MyBoolean

Are there reasons for not storing boolean values in SQL as bit data types?

I'd always stick with the smallest data type I can to store this.

• SQLServer: BIT
• Oracle: NUMBER(1) (or BOOLEAN in PL/SQL)
• MySQL: TINYINT (iirc BOOLEAN maps to this automatically)

Edit: Oracle's BOOLEAN is PL/SQL only, not table definition. Updated answer to reflect this.

Why can't I use a bit field as a boolean expression in a SQL case statement?

Because the bit datatype is not a boolean type, it's a datatype used to optimize the bit storage.

The fact that the string "true" and "false" can be converted to a bit can be misleading, however, quoting from MSDN , a bit is "An integer data type that can take a value of 1, 0, or NULL."

Not able to use Boolean as datatype in C# as a replacement to Bit datatype of SQL Server

A Bit value can take values 0, 1 or null where as a Boolean value can only take values of true or false.

In order to map your bit to a datatype correctly, you need a Nullable Boolean which can take values of true, false or null.

Declare it as such:

Boolean? exmVar;

And you can assign it like this:

exmVar = true;

exmVar = false;

exmVar = null;

If you would like to assign 0 or 1 to your boolean variable, you must use Convert.ToBoolean

exmVar = Convert.ToBoolean(0); // Sets exmVar to false
exmVar = Convert.ToBoolean(1); // Sets exmVar to true

Boolean datatype in SQL causing issues?

I don't think databases have a Boolean type. An alternative to this would be using a BIT instead. Then set this BIT value to either a 1 or 0. Also, It is standard practice for 0 to be construed as FALSE, 1 to be construed as TRUE and Nulls, when allowed, would be reflected as NULL.

What is the difference between BIT and Boolean , Why we can use Boolean in SQL Server 2017

There is no boolean in SQL Server. Instead it uses BIT type to store 0 or 1.

You can refer this for more info

How to use boolean expression in declare/set?

SQL-Server does not know a data-type boolean. The type BIT is a special integer restricted to 0 or 1. Expressions can be evaluated to TRUE or FALSE.

You might use this:

SET @isValid = CASE WHEN @value<>0 THEN 1 ELSE 0 END;

How do I add a Calculated [bit] Data Type field to a SQL Table?

You need to CAST/CONVERT the value returned from the CASE expression. You could likely do this as short as the following:

ALTER TABLE dbo.AutoIndexBoolean 
    ADD BooleanBit AS TRY_CONVERT(bit,Boolean);

If your column Boolean can have other values that 1, or 0 then do something like this:

ALTER TABLE dbo.AutoIndexBoolean 
    ADD BooleanBit AS CONVERT(bit, CASE Boolean WHEN 0 THEN 0 WHEN 1 THEN 1 END);

Of course, the real solution would seem to be change your column Boolean to be a bit, and then you don't need a second column at all.

---

Related Topics