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

%3CLINGO-SUB%20id%3D%22lingo-sub-1484954%22%20slang%3D%22en-US%22%3EShouldn't%20datatype%20bit%20return%20error%20if%20I%20try%20to%20assign%20something%20besides%20a%200%2F1%20or%20True%2Ffalse%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1484954%22%20slang%3D%22en-US%22%3E%3CP%3EThis%20is%20just%20me%20being%20picky%20I%20suppose%2C%20but%20I've%20had%20to%20track%20down%20several%20bugs%20because%20of%20this%20issue.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3Edeclare%26nbsp%3B%40bitvalue%20bit%3C%2FP%3E%3CP%3Eset%20%40bitvalue%20%3D%205%3C%2FP%3E%3CP%3Eselect%26nbsp%3B%40bitvalue%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20would%20expect%20to%20get%20a%20datatype%20error%20when%20I%20try%20to%20assign%20a%20scalar%20variable%20that%20has%20been%20defined%20as%20a%20bit%20data%20type%20to%20%225%22.%20But%20no%2C%20it%20will%20allow%20me%20to%20set%20that%20value%20and%20then%20return%20it%20as%20a%201.%20It%20doesn't%20do%20that%20for%20non-numeric%20values%3A%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3Edeclare%20%40bitvalue%20bit%3C%2FP%3E%3CP%3Eset%20%40bitvalue%20%3D%20'moon'%3C%2FP%3E%3CP%3Eselect%20%40bitvalue%3C%2FP%3E%3CP%3EMsg%20245%2C%20Level%2016%2C%20State%201%2C%20Line%203%3CBR%20%2F%3EConversion%20failed%20when%20converting%20the%20varchar%20value%20'moon'%20to%20data%20type%20bit.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ETo%20me%2C%20'moon'%20has%20about%20as%20much%20in%20common%20with%20'true%22%20as%205%20has%20with%20'true%22%20in%20the%20context%20of%20a%200%20or%20a%201.%20I%20mean%2C%20both%205%20and%201%20are%20numbers%2C%20but%20'true'%20and%20'moon'%20are%20both%20words%20with%20the%20same%20number%20of%20characters.%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIf%20I%20am%20elected%20president%2C%20I%20will%20sign%20an%20executive%20order%20requesting%20that%20the%20sql%20server%20team%20enforce%20data%20type%20bit%20as%20true%2Ffalse%20or%200%2F1%20as%20logic%20demands.%20Until%20then%2C%20I%20will%20sigh%20tragically%20as%20I%20drink%20my%20green%20tea%20and%20fall%204%20hours%20behind%20on%20my%20sprint.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThank%20you%20for%20coming%20to%20my%20xTED%20Talk.%3C%2FP%3E%3C%2FLINGO-BODY%3E
New 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