SQLTeam.com | Weblogs | Forums

Round to top number


#1

Hi.
I want to round a value. For example 4,51 will round to 4,6 or 7,1 will round to 8.
How can i do that?
Thanks.


#2

CEILING() function will do that.


#3

Hi.
So round does not do that? I was thinking that if you change the 2nd variable round(234324,4) will play a part(?).


#4

Hi so i am trying to convert this:
select convert(money,Ceiling(10*(convert(decimal(21,2),2.30)/1.13)*1.18)/10.00)

This is 2.30 / 1.13 = 2,0353
2,0353 * 1.18 = 2,4016 the problem is that the above will round up at 2,50 and not 2,40 as i only need to round up when the second decimal exists. so if this was 2,41 then it would round to 2.50 but not when it is 2.401
Thanks,


#5

Is this correct?
select convert(money,Ceiling(10*convert(decimal(21,2),((2.30)/1.13)*1.18))/10.00)


#6

ROUND will either truncate, or round up or round down. So by default 2.41 when rounded to one decimal place will give you 2.4. But, 2.45 will give you 2.5. You can provide a third parameter to the ROUND function which will truncate the result. So 2.41 as well as 2.45 or even 2.49 will then return 2.40. See here for some more detail: https://msdn.microsoft.com/en-us/library/ms175003.aspx


#7

Sounds like what you are looking for is something like this example:

DECLARE @x DECIMAL(18,5) = 2.30 / 1.13 * 1.18
SELECT ROUND(@x + .04,1)

#8

convert(decimal(21,2),2.30) = 2.30
/1.13 = 2.035398 (its floating point again ... probably not what you want)
*1.18 = 2.40176964 (floating point again)
*10 = 24.01769640 (the "10" is an integer, but the rest of the calculation is Float, so FLOAT is used)
CEILING = 25
/10.00=2.500000 (FLOAT again)
CONVERT(money=2.5000


#9

SELECT ROUND(@x + .04,1) , what exactly we achive with .04,1 ?
Thanks.
Kristen , yes i could not find a better way but it seems to work , at least at the 2 digit test. I had both money and decimal as the initial value so i kinda mixed them up :slight_smile:
If there is a better proposition than James do let me know.
thanks again.


#10

Sorry.
Does this also works on both decimal and money initial values?
As you can see i have a money initial value and a decimal initial value.
Thanks.


#11

Rounding pushes UP anything halfway or above and pushes DOWN anything less than halfway. For example, If you consider rounding to two decimal digits, 2.40499999999... or less will get pushed down to 2.40 and 2.405 to 2.4149999... will get rounded to 2.41. So the demarcation point is 2.405 in this example. But in your case, you want that demarcation point to be 2.401. Adding the 0.04 moves your demarcation point to match with what ROUND function wants you to use.

This should work with money, float, and decimal.


#12

Got it.Thanks James.


#13

Deleted double post.


#14

Did you read the link that JamesK posted? ALL of your questions are answered there