I am working through my OneNote folder of blog ideas, and this one, while being one of the least consequential of them, was the most interesting tonight (and just may help you one day when converting data some day when you are wondering what the heck just happened in a query).

You probably know that the bit datatype has a range of 3 possible “values”. 1, 0, and NULL (hence the double quotes, since NULL really isn’t a value.)  So you probably think that only 3 CAST (or CONVERT) calls should work:

CAST (0 AS bit)
CAST (1 AS bit)
CAST (NULL AS bit) --This one usually used to cast the resulting column in a
                   --table to a type, even if all data is NULL

However, this is not the case. To only have 2 real output values, you can CAST a LOT of data to get those two values. The first, and my least favorite:

SELECT CAST('True' AS bit) as True, CAST('False' AS BIT) as False

This returns:

True  False
----- -----
1     0

Which is pretty horrible in my eyes, because a bit value is NOT a Boolean, even if converting True and False would have you believe that it becomes that. You can’t say:

IF (CAST('True' AS BIT)) SELECT 'Hello';

This returns:

Msg 4145, Level 15, State 1, Line 25
An expression of non-boolean type specified in a context where a condition is expected, near 'SELECT'.

A bit is more of a representation of On and Off than True and False. It is why a standard name for a bit column is a flag column, instead of true or false. You have to code if that value is equal to 1:

IF (CAST('True' AS BIT)) = 1 SELECT 'Hello';

Then you will see the output of ‘Hello’ instead of the error message.

There are no other textual/alpha string values that will cast to a bit value, but the numeric values that will cast to a bit are voluminous (even some that are in string format). Consider the following eight statements:

SELECT CAST(100 AS bit);
SELECT CAST(-100 AS bit);
SELECT CAST(99999999999999999999999999999999999999 AS bit);
SELECT CAST(-99999999999999999999999999999999999999 AS bit);
SELECT CAST(88.999999 AS bit);
SELECT CAST('1' AS bit);
SELECT CAST('2' AS bit);
SELECT CAST('999999' AS bit);

Danged if they didn’t all work, and all return 1. Before I started writing this blog, I would not have guessed that. The first two were interesting, but hey, they are integers, so that makes sense (other than the positive and negative both converting to 1, perhaps.) Then the one with 38 nines was startling, because I didn’t guess it would work a numeric value (I guessed int, or at most, bigint).

Then I casted a numeric with a fractional part. It worked. Then I tried string 1, 2, 999999. All worked. I finally got a failure with something that I did not expect after the numeric 9 to the 38th power did:

SELECT CAST('88.999999' AS bit);

Really? This is going to give this error:

Msg 245, Level 16, State 1, Line 55
Conversion failed when converting the varchar value '88.999999' to data type bit.

But, but… this numeric succeeds:

SELECT CAST('99999999999999999999999999999999999999' AS bit);

The plot thickens when the following statement:

SELECT CAST(999999999999999999999999999999999999999 AS bit);

Fails because the value cannot be converted to a numeric.

Msg 1007, Level 15, State 1, Line 66
The number '999999999999999999999999999999999999999' is out of the range for numeric representation (maximum precision 38).

But make a string with enough nine characters that wouldn’t even fit in a numeric:

SELECT CAST(REPLICATE('9',50) AS bit);

Yep, it succeeds and returns 1.

Turning to a 0 value, there are only a couple of things that will cast to a 0 bit value other than 0 or False. The positive and negative signs, and string values that equate to zero for example:

SELECT CAST('-' AS bit), CAST('+' AS bit), CAST('0000' AS bit)

These all return 0. Deep sigh. 

This has been a bit of a silly blog, I suppose, but it is important to understand what data can cast to what output. Bit is one of the odder datatypes, because while it has the smallest domain of values it can represent  amongst numeric data (0, 1, NULL), it has one of the largest set of values that can be cast to it. This can be convienient, obviously, but it can also be a nightmare when you cast the wrong thing and don’t realize what can happen. Like if your data is filled with rows where -1 was expected to mean False. Or 9999, which you expected to failed but it didn’t.