August 2, 2010 at 10:13 pm
Comments posted to this topic are about the item Round up or down I
August 2, 2010 at 11:15 pm
nice question but ans with care
August 3, 2010 at 12:51 am
Good question...
Prashant Bhatt
Sr Engineer - Application Programming
August 3, 2010 at 3:27 am
So you need to cast it like this?
round(cast(5 as float) / cast(3 as float),1)
August 3, 2010 at 3:33 am
even this works...
set @result = round(cast(5 as float) / 3,1)
i guess either the numerator or denominator should be in the proper format.
cengland0 (8/3/2010)
So you need to cast it like this?round(cast(5 as float) / cast(3 as float),1)
August 3, 2010 at 3:46 am
cengland0 (8/3/2010)
So you need to cast it like this?round(cast(5 as float) / cast(3 as float),1)
As ziangij already says, casting one of the operands is sufficient. And if you do use explicit casting, I'd suggest you to cast to the datatype of the result: decimal(5,2) instead of float.
You can also use implicit casting: ROUND(5.0/3, 1)
The additional .0 forces SQL Server to treat is as numeric. (Even the trailing zero can be omitted, but for readability I prefer "5.0" over "5.")
And to follow up on a promise I made earlier to people who prefer code in a copy/paste-able format, here it is:
DECLARE @Result decimal(5,2);
SET @Result = ROUND(5/3, 1);
PRINT @Result;
August 3, 2010 at 4:52 am
It surprises me why round() acts like floor() when numbers are integers.
I usually prefer the denominator having the ".0" but it is only a matter of preference as the numerator can be ".0".
August 3, 2010 at 5:08 am
Open Minded (8/3/2010)
It surprises me why round() acts like floor() when numbers are integers.
That is not the case. ROUND() does not act like FLOOR().
The "problem" here is the order or evaluation. The expression "5/3" is evaluated first. Since this division uses integers only, the result is integer. This is where results are truncated instead of rounded, so 5/3 evaluates to 1.
The ROUND then rounds 1 to one decimal place, which leaves the value unchanged (of course).
To verify that ROUND does really round correctly, even with integers, run SELECT ROUND(38, -1)
August 3, 2010 at 5:35 am
Yep, I've been bitten by that one more than once. Until you learn that int/int "gotcha" it can be nearly impossible to troubleshoot a script that is experiencing it. Good question.
-----
a haiku...
NULL is not zero
NULL is not an empty string
NULL is the unknown
August 3, 2010 at 6:03 am
Like ronmoses, I, too, knew the answer because I learned the hard way. It's tough to figure-out the first time, but then the second, third, etc, it finally sinks in...
The example is as simple as this - if using hardcoded values.
set @result = round(5/3.0, 1);
Cindy
August 3, 2010 at 6:40 am
Nice question. Had to jump start the grey matter first thing in the morning to get this one.
I especially appreciated all the neatly terminated statements. 😉
August 3, 2010 at 6:59 am
I learned this the hard way. It happened to me twice before I finally filed it away in the long term memory. Nice question, thanks.
August 3, 2010 at 7:12 am
oops..i did wrong..
Learned new point today and is,
Don't think how it is small..think is it correct or not?
Thanks..
August 3, 2010 at 7:36 am
ronmoses (8/3/2010)
Yep, I've been bitten by that one more than once. Until you learn that int/int "gotcha" it can be nearly impossible to troubleshoot a script that is experiencing it. Good question.
Unfortunately, this gets me all the time. I know about the issue, I know the workarounds but it still fails to sink in until after I run the query and don't get the expected results. I failed again when answering the QOTD.
August 3, 2010 at 8:00 am
I think that this "feature" should be changed. Just because two numbers are integers, why assume the answer should also be an integer? :angry:
The greatest enemy of knowledge is not ignorance, it is the illusion of knowledge. - Stephen Hawking
Viewing 15 posts - 1 through 15 (of 28 total)
You must be logged in to reply to this topic. Login to reply