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

Question

I Want to Select opposite Value of AbsenceStatus in tsql ,

AbsenceStatus DataType is Bit

SELECT 
    [Date],
    NOT(AbsenceStatus) As IsWorkday 
FROM 
    tkp_DailyStatistics

`bit` isn't a Boolean; you can't treat it like one. `NOT(1)` doesn't mean anything to the RDBMS. — Thom A, Nov 18 '19 at 12:37

score 1 · Accepted Answer · answered Nov 18 '19 at 12:35

1

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)

answered Nov 18 '19 at 12:35

Gordon Linoff

score 0 · Answer 2 · answered Nov 18 '19 at 12:37

In addition to Gordon's answer +1, we could also try using the modulus here:

SELECT
    [Date],
    (AbsenceStatus + 1) % 2 AS IsWorkday
FROM tkp_DailyStatistics;

This logic works because a zero absent status becomes 1, while a 1 absent status gets incremented to 2, but then the modulus takes it back to zero.

2 Answers2