CODEWITHSUNDEEP
sql-server
user2493287
user2493287

Reputation: 256

Rounding issue in Sql Server 2008 R2 query

can anybody help to solve following rounding problem. Thanks in advance

I have number: 3.1050 Require Output: 3.10 but when I use ROUND(3.1050,2) I am getting result 3.11 hence I cant use ROUND over there.. Following are the some cases which help you solve my question

If value is : 3.1030 Require output : 3.10 If value is :3.1050 Require Output : 3.10 If value is : 3.1080 Require output : 3.11

Upvotes: 3

Views: 4402

Answers (1)

Kash
Kash

Reputation: 9019

What you need is the Round Half Towards Zero rounding method which SQL Server does not provide internally; SQL Server's ROUND() function always follows "Round away from zero" with an additional parameter for truncation instead of rounding.

Hence just write a user function which uses the Round() function around the edge case of last digit being 5 followed by zeroes:

Create Function dbo.RoundToZero(@Val Decimal(32,16), @Digits Int)
Returns Decimal(32,16)
AS
Begin
    Return Case When Abs(@Val - Round(@Val, @Digits, 1)) * Power(10, @Digits+1) = 5
                Then Round(@Val, @Digits, 1)
                Else Round(@Val, @Digits)
                End
End

Source: SQL Server Rounding Methods

Upvotes: 7

Related Questions