Rounding off the value - SQL Server Forums

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]

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

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

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.

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.

bpuccha
Starting Member

34 Posts

Posted - 2013-01-21 : 12:22:26

I tested that,,It worked..Thank u soo much for the reply

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-01-21 : 16:00:19

You are very welcome - glad it helped.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources