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

Trying to perform a single boolean NOT operation, it appears that under MS SQL Server 2005, the following block does not work

DECLARE @MyBoolean bit;
SET @MyBoolean = 0;
SET @MyBoolean = NOT @MyBoolean;
SELECT @MyBoolean;

Instead, I am getting more successful with

  • Insert data in ssrs table in the query
  • Fails when an lock of an object exists in SQL Server
  • Update Table with a “Select query” with a where clause
  • Where clause on a column that's a result of a UDF
  • Getting only Month and Year from SQL DATE
  • Datepart Calculation in Where clause
  • DECLARE @MyBoolean bit;
    SET @MyBoolean = 0;
    SET @MyBoolean = 1 - @MyBoolean;
    SELECT @MyBoolean;
    

    Yet, this looks a bit a twisted way to express something as simple as a negation.

    Am I missing something?

    7 Solutions collect form web for “Boolean 'NOT' in T-SQL not working on 'bit' datatype?”

    Use the ~ operator:

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

    Your solution is a good one… you can also use this syntax to toggle a bit in SQL…

    DECLARE @MyBoolean bit;
    SET @MyBoolean = 0;
    SET @MyBoolean = @MyBoolean ^ 1; 
    SELECT @MyBoolean;
    

    Subtracting the value from 1 looks like it’ll do the trick, but in terms of expressing intent I think I’d prefer to go with:

    SET @MyBoolean = CASE @MyBoolean WHEN 0 THEN 1 ELSE 0 END
    

    It’s more verbose but I think it’s a little easier to understand.

    In SQL 2005 there isn’t a real boolean value, the bit value is something else really.

    A bit can have three states, 1, 0 and null (because it’s data). SQL doesn’t automatically convert these to true or false (although, confusingly SQL enterprise manager will)

    The best way to think of bit fields in logic is as an integer that’s 1 or 0.

    If you use logic directly on a bit field it will behave like any other value variable – i.e. the logic will be true if it has a value (any value) and false otherwise.

    To assign an inverted bit, you’ll need to use the bitwise NOT operator. When using the bitwise NOT operator, ‘~’, you have to make sure your column or variable is declared as a bit.

    This won’t give you zero:

    Select ~1 
    

    This will:

    select ~convert(bit, 1)
    

    So will this:

    declare @t bit
    set @t=1
    select ~@t
    

    BIT is a numeric data type, not boolean. That’s why you can’t apply boolean operators to it.
    SQL Server doesn’t have BOOLEAN data type (not sure about SQL SERVER 2008) so you have to stick with something like @Matt Hamilton’s solution.

    Use ABS to get the absolute value (-1 becomes 1)…

    DECLARE @Trend AS BIT
    SET @Trend = 0
    SELECT @Trend, ABS(@Trend-1)
    
    MS SQL Server is a Microsoft SQL Database product, include sql server standard, sql server management studio, sql server express and so on.