Log in  ::  Register  ::  Not logged in

## Bit by bit

 Author Message bhakti-sonu SSC-Enthusiastic Group: General Forum Members Points: 118 Visits: 78 I try this out with @bit = 0 it gives message "no" if @bit = -500 ,it gives message "yes", if @bit = -1 then also it gives "yes".The Explanation Given for Answer : Bit constants are represented by the numbers 0 or 1, if a number larger than one is used, it is converted to one.but what about the values less than 1, still they consider as 1Please explain me more about the negative values used for bit datataype. alpeshgediya SSC Veteran Group: General Forum Members Points: 208 Visits: 60 for bit datatype, any value which in non-zero is converted to bit 1.try this you will get better understading, declare @bit bitset @bit = -0select @bitdeclare @bit bitset @bit = +0select @bitas mathematical there is no concept of +/- 0 ..is for only understading purpose. ChiragNS SSCertifiable Group: General Forum Members Points: 6333 Visits: 1865 why is'nt BIT treated like other datatypes? "Keep Trying" Marry Krissmess SSC-Enthusiastic Group: General Forum Members Points: 114 Visits: 29 Bob Cullen-434885 (1/28/2010)OK. I am in the 17% who got it wrong - I replied "Error". And now I know. But will somebody kindly explain why BIT is treated any differently to TINYINT, SMALLINT, INT, etc. It is, after all, only a type that has a range of valid values. If the others report an overflow error when I try to assign values outside their permitted range, why not BIT also?I am exactly feeling the same. I think a database should not be so forgiving. In programming languages this may be treated as boolean but here it should be a numeric range and any thing else than 0 or 1 should throw an error. Toreador Hall of Fame Group: General Forum Members Points: 3772 Visits: 8123 Marry Krissmess (2/5/2010)In programming languages this may be treated as boolean but here it should be a numeric range and any thing else than 0 or 1 should throw an error.Why? The Bit datatype is a boolean, so why is it so surprising that it is treated as such? Paul White SSC-Dedicated Group: General Forum Members Points: 33520 Visits: 11359 Marry Krissmess (2/5/2010)I am exactly feeling the same. I think a database should not be so forgiving. In programming languages this may be treated as boolean but here it should be a numeric range and any thing else than 0 or 1 should throw an error.Try it with the strings 'true' and 'false' - that will really upset you! Paul WhiteSQLPerformance.comSQLblog.com@SQL_Kiwi Paul White SSC-Dedicated Group: General Forum Members Points: 33520 Visits: 11359 ChiragNS (1/31/2010)why is'nt BIT treated like other datatypes?It is. The value supplied is implicitly converted to a BIT, according to the conversion rules. Paul WhiteSQLPerformance.comSQLblog.com@SQL_Kiwi shini2007 SSC Rookie Group: General Forum Members Points: 31 Visits: 38 I think":" Converting to bit promotes any nonzero value to 1" not greater than zero only Because DECLARE @bit BITSET @bit = -14IF @bit = 1PRINT 'yes'ELSE PRINT 'no'is also give yes. Paul White SSC-Dedicated Group: General Forum Members Points: 33520 Visits: 11359 shini2007 (9/16/2010)I think":" Converting to bit promotes any nonzero value to 1" not greater than zero only Because DECLARE @bit BITSET @bit = -14IF @bit = 1PRINT 'yes'ELSE PRINT 'no'is also give yes.Non-zero does not mean greater than zero, it means not zero.-14 is not zero, so it is converted to 1, as I said.Does that make sense? Paul WhiteSQLPerformance.comSQLblog.com@SQL_Kiwi kapil_kk SSCertifiable Group: General Forum Members Points: 5114 Visits: 2767 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 OnDeclare @blnFlag bitSelect @blnFlag As [Unset Returns]Set @blnFlag = 0Select @blnFlag As [Set to 0 Returns]Set @blnFlag = 144Select @blnFlag As [Set to 144 Returns]Set @blnFlag = -144Select @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-------------NULLSet to 0 Returns----------------0Set to 144 Returns------------------1Set to -144 Returns-------------------1Set to 'False'--------------0Set to 'True'------------1Msg 245, Level 16, State 1, Line 22Conversion failed when converting the varchar value 'Dave' to data type bit.Dave+1 :-) _______________________________________________________________To get quick answer follow this link:http://www.sqlservercentral.com/articles/Best+Practices/61537/