SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Bit by bit


Bit by bit

Author
Message
bhakti-sonu
bhakti-sonu
SSC-Enthusiastic
SSC-Enthusiastic (118 reputation)SSC-Enthusiastic (118 reputation)SSC-Enthusiastic (118 reputation)SSC-Enthusiastic (118 reputation)SSC-Enthusiastic (118 reputation)SSC-Enthusiastic (118 reputation)SSC-Enthusiastic (118 reputation)SSC-Enthusiastic (118 reputation)

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 1

Please explain me more about the negative values used for bit datataype.
alpeshgediya
alpeshgediya
SSC Veteran
SSC Veteran (208 reputation)SSC Veteran (208 reputation)SSC Veteran (208 reputation)SSC Veteran (208 reputation)SSC Veteran (208 reputation)SSC Veteran (208 reputation)SSC Veteran (208 reputation)SSC Veteran (208 reputation)

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 bit
set @bit = -0
select @bit

declare @bit bit
set @bit = +0
select @bit

as mathematical there is no concept of +/- 0 ..is for only understading purpose.
ChiragNS
ChiragNS
SSCertifiable
SSCertifiable (6.3K reputation)SSCertifiable (6.3K reputation)SSCertifiable (6.3K reputation)SSCertifiable (6.3K reputation)SSCertifiable (6.3K reputation)SSCertifiable (6.3K reputation)SSCertifiable (6.3K reputation)SSCertifiable (6.3K reputation)

Group: General Forum Members
Points: 6333 Visits: 1865
why is'nt BIT treated like other datatypes?

"Keep Trying"
Marry Krissmess
Marry Krissmess
SSC-Enthusiastic
SSC-Enthusiastic (114 reputation)SSC-Enthusiastic (114 reputation)SSC-Enthusiastic (114 reputation)SSC-Enthusiastic (114 reputation)SSC-Enthusiastic (114 reputation)SSC-Enthusiastic (114 reputation)SSC-Enthusiastic (114 reputation)SSC-Enthusiastic (114 reputation)

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
Toreador
Hall of Fame
Hall of Fame (3.8K reputation)Hall of Fame (3.8K reputation)Hall of Fame (3.8K reputation)Hall of Fame (3.8K reputation)Hall of Fame (3.8K reputation)Hall of Fame (3.8K reputation)Hall of Fame (3.8K reputation)Hall of Fame (3.8K reputation)

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
Paul White
SSC-Dedicated
SSC-Dedicated (33K reputation)SSC-Dedicated (33K reputation)SSC-Dedicated (33K reputation)SSC-Dedicated (33K reputation)SSC-Dedicated (33K reputation)SSC-Dedicated (33K reputation)SSC-Dedicated (33K reputation)SSC-Dedicated (33K reputation)

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 White
SQLPerformance.com
SQLblog.com
@SQL_Kiwi
Paul White
Paul White
SSC-Dedicated
SSC-Dedicated (33K reputation)SSC-Dedicated (33K reputation)SSC-Dedicated (33K reputation)SSC-Dedicated (33K reputation)SSC-Dedicated (33K reputation)SSC-Dedicated (33K reputation)SSC-Dedicated (33K reputation)SSC-Dedicated (33K reputation)

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 White
SQLPerformance.com
SQLblog.com
@SQL_Kiwi
shini2007
shini2007
SSC Rookie
SSC Rookie (31 reputation)SSC Rookie (31 reputation)SSC Rookie (31 reputation)SSC Rookie (31 reputation)SSC Rookie (31 reputation)SSC Rookie (31 reputation)SSC Rookie (31 reputation)SSC Rookie (31 reputation)

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 BIT
SET @bit = -14

IF @bit = 1PRINT 'yes'ELSE PRINT 'no'

is also give yes.
Paul White
Paul White
SSC-Dedicated
SSC-Dedicated (33K reputation)SSC-Dedicated (33K reputation)SSC-Dedicated (33K reputation)SSC-Dedicated (33K reputation)SSC-Dedicated (33K reputation)SSC-Dedicated (33K reputation)SSC-Dedicated (33K reputation)SSC-Dedicated (33K reputation)

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 BIT
SET @bit = -14

IF @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 White
SQLPerformance.com
SQLblog.com
@SQL_Kiwi
kapil_kk
kapil_kk
SSCertifiable
SSCertifiable (5.1K reputation)SSCertifiable (5.1K reputation)SSCertifiable (5.1K reputation)SSCertifiable (5.1K reputation)SSCertifiable (5.1K reputation)SSCertifiable (5.1K reputation)SSCertifiable (5.1K reputation)SSCertifiable (5.1K reputation)

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 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

+1 :-)

_______________________________________________________________
To get quick answer follow this link:
http://www.sqlservercentral.com/articles/Best+Practices/61537/
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search