SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2000 Forums
 SQL Server Development (2000)
 Rounding off the value
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

bpuccha
Starting Member

34 Posts

Posted - 01/17/2013 :  15:37:58  Show Profile  Reply with Quote
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.

Edited by - bpuccha on 01/17/2013 15:49:54

sodeep
Flowing Fount of Yak Knowledge

USA
7174 Posts

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

bpuccha
Starting Member

34 Posts

Posted - 01/17/2013 :  16:18:12  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

3633 Posts

Posted - 01/17/2013 :  17:44:41  Show Profile  Reply with Quote
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 - 01/18/2013 :  09:55:23  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

3633 Posts

Posted - 01/18/2013 :  13:16:19  Show Profile  Reply with Quote
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 - 01/21/2013 :  12:22:26  Show Profile  Reply with Quote
I tested that,,It worked..Thank u soo much for the reply

Edited by - bpuccha on 01/21/2013 12:25:29
Go to Top of Page

James K
Flowing Fount of Yak Knowledge

3633 Posts

Posted - 01/21/2013 :  16:00:19  Show Profile  Reply with Quote
You are very welcome - glad it helped.
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.09 seconds. Powered By: Snitz Forums 2000