CODEWITHSUNDEEP
sqlsql-serverrounding
K.Tom
K.Tom

Reputation: 185

Round the decimal Values in SQL Server

I have a column that has Numerical data(float) and need to round up the only the decimal values. 

    Actual Value  Rounded Value
    2370.09 -->   2370.10
    2370.07 -->   2370.08
    2370.05 -->   2370.06
    2370.01 -->   2370.02
    2370.02 -->   2370.02
    2370.04 -->   2370.04
    2370.06 -->   2370.06
    2370.08 -->   2370.08

If the decimal value(after period) is odd then it should be increased by 0.01 and if its even then there is no change in the value.

I've tried SELECT ROUND(Actual Value, 1)

How can I make this work?

Upvotes: 2

Views: 497

Answers (2)

S3S
S3S

Reputation: 25152

I would use a case statement with modulus. This is an easy method to check the last digit and see if it's even or odd, and then handle the simple addition.

declare @var decimal(6,2) = 2370.07
select
    case 
        when right(@var,1) % 2 != 0 
        then @var + 0.01 
        else @var 
    end

Or, using your schema

select
    [Actual Value]
    ,[RoundedValue] = 
                      case 
                           when right([Actual Value],1) % 2 != 0 
                           then [Actual Value]+ 0.01 
                           else [Actual Value]
                      end
From Your Table

Upvotes: 2

Hannah Vernon
Hannah Vernon

Reputation: 3472

Building on the modulus example in the other answer provided by @scsimon, you could do this without a case statement like this:

DECLARE @t TABLE
(
    n numeric(10,2) NOT NULL
);
INSERT INTO @t (n)
VALUES (2370.09)
    , (2370.07)
    , (2370.06)
    , (0)
    , (1.01)
    , (1.09)
    , (1.9)
    , (9999.99);

SELECT (t.n + (t.n % 0.02))
FROM @t t;

╔══════════════════╗
║ (No column name) ║
╠══════════════════╣
║          2370.10 ║
║          2370.08 ║
║          2370.06 ║
║             0.00 ║
║             1.02 ║
║             1.10 ║
║             1.90 ║
║         10000.00 ║
╚══════════════════╝

Upvotes: 3

Related Questions