Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 SQL Server 2000 Forums
 SQL Server Development (2000)
 Rounding off the value

Author  Topic 

bpuccha
Starting Member

34 Posts

Posted - 2013-01-17 : 15:37:58
I have to round off the value to 2 decimal places, I did that through round function as follows

select round(1023.245,2,0)----1023.25(But I want it as 1023.24,it should round down,instead of up)
select round(1023.246,2,0)----1023.25

But I dont want to round the value if the 3rd digit after the decimal is 5.So the first value should come as 1023.24

We can use round(1023.245,2,1), but it always trucates the value.I need to round down only it has 5.

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2013-01-17 : 16:12:57
[code]Select case when right(cast(1023.242 as Varchar(20)),1) > 5 then round(1023.242,2) else round(1023.242,2,1) end [/code]
Go to Top of Page

bpuccha
Starting Member

34 Posts

Posted - 2013-01-17 : 16:18:12
I can do that, but in my stored procedure i am caluculating 10 diff values based on that amt(1023.245), the calculations are pretty complex like below.So evertime i have to use case statement,Is there any other way???

(@p_due_amt/2)+(@p_due_amt - (round((@p_due_amt/2),2,0)*2))
Go to Top of Page

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-01-17 : 17:44:41
If you include a subtract of 0.001 on all occurrences where you want this rule to be applied that should do it - for example:
SELECT ROUND(1023.245-0.001,2,0)
I prefer casting to decimal of the appropriate scale rather than rounding because of cases like the following where the results can overflow
SELECT ROUND(999.996,2,0)

Msg 8115, Level 16, State 2, Line 1
Arithmetic overflow error converting expression to data type numeric.


SELECT CAST(999.996 AS DECIMAL(18,2))
Go to Top of Page

bpuccha
Starting Member

34 Posts

Posted - 2013-01-18 : 09:55:23
Thanks for the reply. But 1023.245 is not a constant value, it varies depends on the input parameters.So i can't substract 0.001 from that value everytime.
Go to Top of Page

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-01-18 : 13:16:19
I was only showing any example. It works the same way with a constant, variable, or column - i.e, subtract 0.001 and round to 2 decmials, and you will be effectively rounding down any fractional part that has a 5 in the third decimal place. All other numbers would behave the same way as before.
Go to Top of Page

bpuccha
Starting Member

34 Posts

Posted - 2013-01-21 : 12:22:26
I tested that,,It worked..Thank u soo much for the reply
Go to Top of Page

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-01-21 : 16:00:19
You are very welcome - glad it helped.
Go to Top of Page
   

- Advertisement -