Recent PostsRecent Posts Popular TopicsPopular Topics
 Home Search Members Calendar Who's On

 Round up or down I Rate Topic Display Mode Topic Options
Author
 Message
 Posted Monday, August 2, 2010 10:13 PM
 SSCertifiable Group: General Forum Members Last Login: 2 days ago @ 1:48 PM Points: 7,765, Visits: 11,376
 Comments posted to this topic are about the item Round up or down I Hugo Kornelis, SQL Server MVPVisit my SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis
Post #962576
 Posted Monday, August 2, 2010 11:15 PM
 SSC Veteran Group: General Forum Members Last Login: Wednesday, May 11, 2016 1:17 AM Points: 282, Visits: 100
 nice question but ans with care
Post #962591
 Posted Tuesday, August 3, 2010 12:51 AM
 SSC-Enthusiastic Group: General Forum Members Last Login: Friday, March 4, 2016 3:49 AM Points: 118, Visits: 177
 Good question... Prashant BhattSr Engineer - Application Programming
Post #962620
 Posted Tuesday, August 3, 2010 3:27 AM
 Ten Centuries Group: General Forum Members Last Login: Saturday, March 5, 2016 7:55 PM Points: 1,354, Visits: 1,300
 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
 SSCrazy Group: General Forum Members Last Login: Tuesday, April 19, 2016 5:42 AM Points: 2,831, Visits: 374
 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 Group: General Forum Members Last Login: 2 days ago @ 1:48 PM Points: 7,765, Visits: 11,376
 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 MVPVisit my SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis
Post #962673
 Posted Tuesday, August 3, 2010 4:52 AM
 SSC-Enthusiastic Group: General Forum Members Last Login: Monday, August 18, 2014 4:04 AM Points: 176, 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".
Post #962706
 Posted Tuesday, August 3, 2010 5:08 AM
 SSCertifiable Group: General Forum Members Last Login: 2 days ago @ 1:48 PM Points: 7,765, Visits: 11,376
 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 MVPVisit my SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis
Post #962712
 Posted Tuesday, August 3, 2010 5:35 AM
 Ten Centuries Group: General Forum Members Last Login: Wednesday, May 25, 2016 11:36 AM Points: 1,045, 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 zeroNULL is not an empty stringNULL is the unknown
Post #962721
 Posted Tuesday, August 3, 2010 6:03 AM
 SSChasing Mays Group: General Forum Members Last Login: 2 days ago @ 8:52 AM Points: 638, Visits: 1,594
 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

 Permissions