Converting numeric values looks like to be intuitive but it can still surprise you. Doing conversion from various different data types means that every value which isn’t 0 (zero) will be converted as TRUE. And that’s not something you will expect naturally. You can test this behavior by yourself. Simply copy the following statements to SSMS and execute them:
SELECT CAST(-5 AS BIT) SELECT CAST(0xAAAAA AS BIT) SELECT CAST(5555555-000-00000000-000000000000 AS BIT)
All statements are returning 1 (TRUE). Don’t get confused by the last statement which looks like to be UNIQUEIDENTIFIER. It is not. It’s in fact simple subtraction of zeroes from number 5555555 and for the result, the value of BIT is 1 all the time.
More attention is the need for the following statements:
SELECT CAST(0x AS BIT) SELECT CAST(0x000 AS BIT) SELECT CAST(0000000-000-000000000-000000000000 AS BIT)
They all have converted BIT value 0 (FALSE) because their numeric value is 0 and conversion from 0 to BIT is 0 (FALSE).