| Author |
 Topic  |
|
|
bpuccha
Starting Member
29 Posts |
 Posted - 01/17/2013 : 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. |
Edited by - bpuccha on 01/17/2013 15:49:54
|
|
|
sodeep
Flowing Fount of Yak Knowledge
USA
7173 Posts |
Posted - 01/17/2013 : 16:12:57
|
Select case when right(cast(1023.242 as Varchar(20)),1) > 5 then round(1023.242,2) else round(1023.242,2,1) end |
 |
|
|
bpuccha
Starting Member
29 Posts |
Posted - 01/17/2013 : 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
Flowing Fount of Yak Knowledge
1500 Posts |
Posted - 01/17/2013 : 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 overflowSELECT 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
29 Posts |
Posted - 01/18/2013 : 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
Flowing Fount of Yak Knowledge
1500 Posts |
Posted - 01/18/2013 : 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
29 Posts |
Posted - 01/21/2013 : 12:22:26
|
| I tested that,,It worked..Thank u soo much for the reply |
Edited by - bpuccha on 01/21/2013 12:25:29 |
|
|
|
James K
Flowing Fount of Yak Knowledge
1500 Posts |
Posted - 01/21/2013 : 16:00:19
|
| You are very welcome - glad it helped. |
|
|
| |
Topic |
|
Rounding off the value
