

Ten Centuries
Group: General Forum Members
Last Login: Monday, June 6, 2016 1:51 PM
Points: 1,400,
Visits: 3,059


malleswarareddy_m (4/4/2010) Good QOD.This Question gives good information about bit datatypes. when we use space or all zero it bit will convert it into zero.If we give numeric(numbers) it will convert it into one. . . . .
This is true for other numeric datatypes as well. Spaces or empty strings are implicitly converted to zero
DECLARE @MyNum int SET @MyNum = ' '  attempt arithmetic operation Select @myNum * 4  result is 0, not an error.




Mr or Mrs. 500
Group: General Forum Members
Last Login: Friday, April 25, 2014 8:31 AM
Points: 513,
Visits: 430


john.arnott (4/5/2010)
This is true for other numeric datatypes as well. Spaces or empty strings are implicitly converted to zero DECLARE @MyNum int SET @MyNum = ' '  attempt arithmetic operation Select @myNum * 4  result is 0, not an error. Good point, but I would like it more if SQL Server throws an exception and let developers know upfront that it's wrong to assign string/character values to an integer data type.




Right there with Babe
Group: General Forum Members
Last Login: Tuesday, December 7, 2010 12:55 AM
Points: 771,
Visits: 504


It will throw an error if you attempt to assign a NONempty string to an integer data type.
I can see your point for an empty string/character value, but it does come in handy when you need to work with data where someone has set all the columns that should be integer to text




SSCommitted
Group: General Forum Members
Last Login: Wednesday, July 29, 2015 11:50 PM
Points: 1,967,
Visits: 1,189


vkkirov (4/5/2010)
malleswarareddy_m (4/4/2010) declare @bit bit set @bit='00000' select @bit
This gives 1 as output.This must be a typo because the result is 0.
Yes it will give zero as ouptut. Some typing mistake.
Malleswarareddy I.T.Analyst MCITP(70451)




SSCommitted
Group: General Forum Members
Last Login: Wednesday, July 29, 2015 11:50 PM
Points: 1,967,
Visits: 1,189


john.arnott (4/5/2010)
malleswarareddy_m (4/4/2010) Good QOD.This Question gives good information about bit datatypes. when we use space or all zero it bit will convert it into zero.If we give numeric(numbers) it will convert it into one. . . . .
This is true for other numeric datatypes as well. Spaces or empty strings are implicitly converted to zero DECLARE @MyNum int SET @MyNum = ' '  attempt arithmetic operation Select @myNum * 4  result is 0, not an error.
I think it will throw error when converting it to string datatype except(TRUE/False)
Malleswarareddy I.T.Analyst MCITP(70451)




SSCrazy
Group: General Forum Members
Last Login: Tuesday, April 19, 2016 5:42 AM
Points: 2,722,
Visits: 374


good question, thanks




Ten Centuries
Group: General Forum Members
Last Login: Monday, June 6, 2016 1:51 PM
Points: 1,400,
Visits: 3,059


malleswarareddy_m (4/5/2010)
john.arnott (4/5/2010)
malleswarareddy_m (4/4/2010) Good QOD.This Question gives good information about bit datatypes. when we use space or all zero it bit will convert it into zero.If we give numeric(numbers) it will convert it into one. . . . .
This is true for other numeric datatypes as well. Spaces or empty strings are implicitly converted to zero DECLARE @MyNum int SET @MyNum = ' '  attempt arithmetic operation Select @myNum * 4  result is 0, not an error. I think it will throw error when converting it to string datatype except(TRUE/False) Sorry?? What will cause an error?
Your statement looks to be reversed of what you may have meant  aren't we talking about converting from strings to numerics? My example of implicit conversion from a string to an int does not cause an error, nor would an empty string, the point being that it's not just conversions to the bit type where empty or blank strings result in zero.
I didn't find a BOL or MSDN article on this specific behavior, but from what I did find, it seems clear that the SQL engine sets the target to zero, then adds the numeric evaluation of the string, ignoring spaces. With nothing else to evaluate, the result remains zero.
You say you "think it will throw error". Did you try it?




SSCommitted
Group: General Forum Members
Last Login: Wednesday, July 29, 2015 11:50 PM
Points: 1,967,
Visits: 1,189


john.arnott (4/6/2010)
malleswarareddy_m (4/5/2010)
john.arnott (4/5/2010)
malleswarareddy_m (4/4/2010) Good QOD.This Question gives good information about bit datatypes. when we use space or all zero it bit will convert it into zero.If we give numeric(numbers) it will convert it into one. . . . .
This is true for other numeric datatypes as well. Spaces or empty strings are implicitly converted to zero DECLARE @MyNum int SET @MyNum = ' '  attempt arithmetic operation Select @myNum * 4  result is 0, not an error. I think it will throw error when converting it to string datatype except(TRUE/False) Sorry?? What will cause an error? Your statement looks to be reversed of what you may have meant  aren't we talking about converting from strings to numerics? My example of implicit conversion from a string to an int does not cause an error, nor would an empty string, the point being that it's not just conversions to the bit type where empty or blank strings result in zero. I didn't find a BOL or MSDN article on this specific behavior, but from what I did find, it seems clear that the SQL engine sets the target to zero, then adds the numeric evaluation of the string, ignoring spaces. With nothing else to evaluate, the result remains zero. You say you " think it will throw error". Did you try it?
your Example is correct.when convert the bit into string it will thrown an error.
Malleswarareddy I.T.Analyst MCITP(70451)




SSCEnthusiastic
Group: General Forum Members
Last Login: Friday, January 10, 2014 4:09 AM
Points: 173,
Visits: 101





SSCrazy Eights
Group: General Forum Members
Last Login: Friday, July 22, 2016 6:18 PM
Points: 9,932,
Visits: 11,342




