Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase «««123

Bit by bit Expand / Collapse
Author
Message
Posted Thursday, January 28, 2010 10:05 PM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Friday, March 12, 2010 3:15 AM
Points: 66, 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.


Post #855758
Posted Thursday, January 28, 2010 10:15 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Monday, March 25, 2013 7:14 AM
Points: 196, 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.
Post #855762
Posted Sunday, January 31, 2010 10:24 PM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Tuesday, September 23, 2014 8:08 AM
Points: 2,365, Visits: 1,846
why is'nt BIT treated like other datatypes?

"Keep Trying"
Post #856934
Posted Friday, February 5, 2010 5:12 PM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Thursday, April 25, 2013 2:25 PM
Points: 94, 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.

Post #860936
Posted Monday, February 8, 2010 2:02 AM
SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Yesterday @ 10:26 AM
Points: 1,780, Visits: 6,475
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?
Post #861483
Posted Tuesday, March 30, 2010 10:13 AM


SSCrazy Eights

SSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy Eights

Group: General Forum Members
Last Login: Friday, October 17, 2014 8:13 AM
Points: 9,926, Visits: 11,188
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
SQL Server MVP
SQLblog.com
@SQL_Kiwi
Post #893006
Posted Tuesday, March 30, 2010 10:14 AM


SSCrazy Eights

SSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy Eights

Group: General Forum Members
Last Login: Friday, October 17, 2014 8:13 AM
Points: 9,926, Visits: 11,188
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
SQL Server MVP
SQLblog.com
@SQL_Kiwi
Post #893008
Posted Thursday, September 16, 2010 11:46 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Thursday, February 6, 2014 6:56 AM
Points: 29, 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.
Post #987900
Posted Friday, September 17, 2010 1:32 AM


SSCrazy Eights

SSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy Eights

Group: General Forum Members
Last Login: Friday, October 17, 2014 8:13 AM
Points: 9,926, Visits: 11,188
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
SQL Server MVP
SQLblog.com
@SQL_Kiwi
Post #987934
Posted Thursday, September 12, 2013 5:28 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Monday, October 20, 2014 4:54 AM
Points: 1,921, Visits: 2,345
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/
Post #1494097
« Prev Topic | Next Topic »

Add to briefcase «««123

Permissions Expand / Collapse