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

Round up or down I Expand / Collapse
Author
Message
Posted Monday, August 2, 2010 10:13 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 3:20 PM
Points: 5,926, Visits: 8,175
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
Post #962576
Posted Monday, August 2, 2010 11:15 PM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Wednesday, July 16, 2014 6:22 AM
Points: 271, Visits: 96
nice question but ans with care
Post #962591
Posted Tuesday, August 3, 2010 12:51 AM


SSC-Enthusiastic

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

Group: General Forum Members
Last Login: Friday, July 18, 2014 10:35 AM
Points: 118, Visits: 173
Good question...



Prashant Bhatt
Sr Engineer - Application Programming
Post #962620
Posted Tuesday, August 3, 2010 3:27 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Thursday, January 24, 2013 9:59 PM
Points: 1,354, Visits: 1,299
So you need to cast it like this?

round(cast(5 as float) / cast(3 as float),1)
Post #962662
Posted Tuesday, August 3, 2010 3:33 AM
SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Monday, June 30, 2014 2:51 AM
Points: 1,972, Visits: 369
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)
Post #962666
Posted Tuesday, August 3, 2010 3:46 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 3:20 PM
Points: 5,926, Visits: 8,175
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
Post #962673
Posted Tuesday, August 3, 2010 4:52 AM
SSC-Enthusiastic

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

Group: General Forum Members
Last Login: Friday, June 6, 2014 12:28 AM
Points: 176, Visits: 565
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".
Post #962706
Posted Tuesday, August 3, 2010 5:08 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 3:20 PM
Points: 5,926, Visits: 8,175
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
Post #962712
Posted Tuesday, August 3, 2010 5:35 AM


SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Friday, July 25, 2014 6:26 AM
Points: 846, Visits: 850
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
Post #962721
Posted Tuesday, August 3, 2010 6:03 AM


SSChasing Mays

SSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing Mays

Group: General Forum Members
Last Login: Monday, June 9, 2014 7:16 AM
Points: 634, Visits: 1,526
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
Post #962728
« Prev Topic | Next Topic »

Add to briefcase 123»»»

Permissions Expand / Collapse