Dave62 (1/28/2010)

---

A little more information about the BIT type. The bit type has 3 states: NULL, 0, and 1.

When first declared and unset a NULL will be returned.

Only a 0 or the string 'False' will return a 0.

Any positive or negative number or the string 'True' will return a 1.

Any string other than 'True' or 'False' will return an error.

The following script illustrates the points above:

Set NOCOUNT On

Declare @blnFlag bit

Select @blnFlag As [Unset Returns]

Set @blnFlag = 0

Select @blnFlag As [Set to 0 Returns]

Set @blnFlag = 144

Select @blnFlag As [Set to 144 Returns]

Set @blnFlag = -144

Select @blnFlag As [Set to -144 Returns]

Set @blnFlag = 'False'

Select @blnFlag As [Set to 'False']

Set @blnFlag = 'True'

Select @blnFlag As [Set to 'True']

Set @blnFlag = 'Dave'

Select @blnFlag

The following resuts are returned:

Unset Returns

-------------

NULL

Set to 0 Returns

----------------

0

Set to 144 Returns

------------------

1

Set to -144 Returns

-------------------

1

Set to 'False'

--------------

0

Set to 'True'

------------

1

Msg 245, Level 16, State 1, Line 22

Conversion failed when converting the varchar value 'Dave' to data type bit.

Dave