How to Flip a Bit in SQL Server

How do I flip a bit in SQL Server?

Yes, the ~ operator will work.

update foo
set Sync = ~@IsNew

How to flip bit fields in T-SQL?

You don't need a bitwise-not for this -- just XOR it with 1 / true.

To check it:

select idColumn, bitFieldY, bitFieldY ^ 1 as Toggled
from tableX

To update:

update tableX
set bitFieldY = bitFieldY ^ 1
where ...

MSDN T-SQL Exclusive-OR (^)

Bit-flipping operations in T-SQL

Use XOR:

SELECT value ^ 256

So in your case, SELECT 143 ^ 256 will indeed return 399. If you want to pass in the exponent as well:

SELECT value ^ POWER(2, power)

How to opposite Select From a Bit Value in SQL without Use Case when

You can use -:

select 1 - b

Here is a db<>fiddle.

Note: This converts the value to a number, so you might really want:

select convert(bit, 1 - b)

Is there an elegant way to Invert a Bit value in an SQL insert Statement?

I did not test this myself, but you should be able to use the bitwise negation operator, ~ on a bit:

INSERT INTO MYTABLE (AllowEdit) 
(SELECT ~PreventEdit FROM SourceTable)

Reverse Data With BIT TYPE for SQL Server

To negate the value of all bit fields do it in one operation. You can use a CASE

UPDATE table1
SET    flag = CASE flag
                WHEN 1 THEN 0
                WHEN 0 THEN 1
              END 

Or Bitwise Exclusive OR

UPDATE table1
SET    flag = flag ^ 1

How to flip a bit switch based on events in date sequence without using SQL CURSOR?

I think this will help you

CREATE TABLE DateList(Period date) ;

CREATE TABLE EVENTS (Period date, Event varchar(5));

INSERT INTO Events(Period, Event)
VALUES ('2001-01-05', 'on'),
       ('2001-01-08', 'off');

INSERT INTO DateList(Period)
VALUES ('2001-01-01'),
       ('2001-01-02'),
       ('2001-01-03'),
       ('2001-01-04'),
       ('2001-01-05'),
       ('2001-01-06'),
       ('2001-01-07'),
       ('2001-01-08'),
       ('2001-01-09'),
       ('2001-01-10'),
       ('2001-01-11');

SELECT d.Period AS
START , j.Event,
        CASE
            WHEN e.Event='on' THEN 1
            ELSE 0
        END [Bit]
FROM DateList d
LEFT JOIN EVENTS j ON j.Period=d.Period
LEFT JOIN
  (SELECT Period AS PeriodStart,
          isnull(dateadd(DAY, -1, lead(Period, 1) over(
                                                       ORDER BY Period)), Period) PeriodEnd,
          Event
   FROM EVENTS) E ON d.Period BETWEEN e.PeriodStart AND e.PeriodEnd;

How to negate a bit column value in SQL

You can use bitwise NOT operator:

update mytable set IsEditable = ~IsEditable

How to flip random bits in SQL

The 1 * is still yielding a fractional number & given that cast(0.1 as bit) will yeild 1 as will cast(0.9 as bit) the updates are all set to 1.

You could;

update Planned set IsPlannable = case when rand(cast(newid() as binary(8))) < 0.5 then 0 else 1 end

---

Related Topics