Shouldn't datatype bit return error if I try to assign something besides a 0/1 or True/false?

Copper Contributor

This is just me being picky I suppose, but I've had to track down several bugs because of this issue.


declare @bitvalue bit

set @bitvalue = 5

select @bitvalue


I would expect to get a datatype error when I try to assign a scalar variable that has been defined as a bit data type to "5". But no, it will allow me to set that value and then return it as a 1. It doesn't do that for non-numeric values:


declare @bitvalue bit

set @bitvalue = 'moon'

select @bitvalue

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


To me, 'moon' has about as much in common with 'true" as 5 has with 'true" in the context of a 0 or a 1. I mean, both 5 and 1 are numbers, but 'true' and 'moon' are both words with the same number of characters. 


If I am elected president, I will sign an executive order requesting that the sql server team enforce data type bit as true/false or 0/1 as logic demands. Until then, I will sigh tragically as I drink my green tea and fall 4 hours behind on my sprint.


Thank you for coming to my xTED Talk.

0 Replies