Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Round up or down I


Round up or down I

Author
Message
Hugo Kornelis
Hugo Kornelis
SSCrazy Eights
SSCrazy Eights (8.9K reputation)SSCrazy Eights (8.9K reputation)SSCrazy Eights (8.9K reputation)SSCrazy Eights (8.9K reputation)SSCrazy Eights (8.9K reputation)SSCrazy Eights (8.9K reputation)SSCrazy Eights (8.9K reputation)SSCrazy Eights (8.9K reputation)

Group: General Forum Members
Points: 8915 Visits: 11740
Comments posted to this topic are about the item Round up or down I


Hugo Kornelis, SQL Server MVP
Visit my SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis
udayroy15
udayroy15
Old Hand
Old Hand (306 reputation)Old Hand (306 reputation)Old Hand (306 reputation)Old Hand (306 reputation)Old Hand (306 reputation)Old Hand (306 reputation)Old Hand (306 reputation)Old Hand (306 reputation)

Group: General Forum Members
Points: 306 Visits: 100
nice question but ans with care
prashant.bhatt
prashant.bhatt
SSC-Enthusiastic
SSC-Enthusiastic (122 reputation)SSC-Enthusiastic (122 reputation)SSC-Enthusiastic (122 reputation)SSC-Enthusiastic (122 reputation)SSC-Enthusiastic (122 reputation)SSC-Enthusiastic (122 reputation)SSC-Enthusiastic (122 reputation)SSC-Enthusiastic (122 reputation)

Group: General Forum Members
Points: 122 Visits: 177
Good question...

Prashant Bhatt
Sr Engineer - Application Programming
cengland0
cengland0
SSCommitted
SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)

Group: General Forum Members
Points: 1536 Visits: 1300
So you need to cast it like this?

round(cast(5 as float) / cast(3 as float),1)
ziangij
ziangij
SSCrazy
SSCrazy (3K reputation)SSCrazy (3K reputation)SSCrazy (3K reputation)SSCrazy (3K reputation)SSCrazy (3K reputation)SSCrazy (3K reputation)SSCrazy (3K reputation)SSCrazy (3K reputation)

Group: General Forum Members
Points: 2987 Visits: 376
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)

Hugo Kornelis
Hugo Kornelis
SSCrazy Eights
SSCrazy Eights (8.9K reputation)SSCrazy Eights (8.9K reputation)SSCrazy Eights (8.9K reputation)SSCrazy Eights (8.9K reputation)SSCrazy Eights (8.9K reputation)SSCrazy Eights (8.9K reputation)SSCrazy Eights (8.9K reputation)SSCrazy Eights (8.9K reputation)

Group: General Forum Members
Points: 8915 Visits: 11740
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;




Hugo Kornelis, SQL Server MVP
Visit my SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis
Open Minded
Open Minded
SSC-Enthusiastic
SSC-Enthusiastic (190 reputation)SSC-Enthusiastic (190 reputation)SSC-Enthusiastic (190 reputation)SSC-Enthusiastic (190 reputation)SSC-Enthusiastic (190 reputation)SSC-Enthusiastic (190 reputation)SSC-Enthusiastic (190 reputation)SSC-Enthusiastic (190 reputation)

Group: General Forum Members
Points: 190 Visits: 567
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".
Hugo Kornelis
Hugo Kornelis
SSCrazy Eights
SSCrazy Eights (8.9K reputation)SSCrazy Eights (8.9K reputation)SSCrazy Eights (8.9K reputation)SSCrazy Eights (8.9K reputation)SSCrazy Eights (8.9K reputation)SSCrazy Eights (8.9K reputation)SSCrazy Eights (8.9K reputation)SSCrazy Eights (8.9K reputation)

Group: General Forum Members
Points: 8915 Visits: 11740
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)


Hugo Kornelis, SQL Server MVP
Visit my SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis
ronmoses
ronmoses
Ten Centuries
Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)

Group: General Forum Members
Points: 1123 Visits: 996
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

CavyPrincess
CavyPrincess
Say Hey Kid
Say Hey Kid (687 reputation)Say Hey Kid (687 reputation)Say Hey Kid (687 reputation)Say Hey Kid (687 reputation)Say Hey Kid (687 reputation)Say Hey Kid (687 reputation)Say Hey Kid (687 reputation)Say Hey Kid (687 reputation)

Group: General Forum Members
Points: 687 Visits: 1644
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
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