Rounding Off to Two Decimal Places in Sql

Rounding off to two decimal places in SQL

You could cast your result as numeric(x,2). Where x <= 38.

select
    round(630/60.0,2),
    cast(round(630/60.0,2) as numeric(36,2))

Returns

10.500000    10.50

Round a value to two decimal places in SQL

Instead of round() convert to a decimal:

select cast(600.000 + 0.5 as decimal(10, 2) )

round() changes the value but it might not change the type of the result. Hence, you might still see extra decimal points (depending on the database and the application). Converting to a decimal with two digits of precision converts both the value and the type.

Integer division round off to 2 decimal places in SQL Server

When we divide we can use an integer, which will produce an integer result, or a decimal by adding a decimal point (with or without a zero) which will give a decimal result with the number of decimal places determined by the format, or a floating point by adding an e, which will use the number of significant decimal places without trailing zeros. The following test schema demonstrates the difference between them.

I would seem that ROUND(2e/3,2) is the most concise way to arrive at 2 decimal places.

SELECT 
  3e/5 FloatingPoint,
  3./5 DecimalDivision,
  CONVERT(DECIMAL(3,2), 3./5) DecimalDivision,
  Round(3e/5,2) RoundedFloat

FloatingPoint | DecimalDivision | DecimalDivision | RoundedFloat
------------: | --------------: | --------------: | -----------:
          0.6 |        0.600000 |            0.60 |          0.6

SELECT 
  2e/3 FloatingPoint,
  2./3 DecimalDivision,
  CONVERT(DECIMAL(3,2), 2./3) DecimalDivision,
  Round(2e/3,2) RoundedFloat

    FloatingPoint | DecimalDivision | DecimalDivision | RoundedFloat
----------------: | --------------: | --------------: | -----------:
0.666666666666667 |        0.666666 |            0.67 |         0.67

db<>fiddle here

Rounding to 2 decimal places in SQL

you may try the TO_CHAR function to convert the result

e.g.

SELECT TO_CHAR(92, '99.99') AS RES FROM DUAL

SELECT TO_CHAR(92.258, '99.99') AS RES FROM DUAL

Hope it helps

T-SQL round down to 2 decimals

You can use Window Functions to add the missing portion to the number that lost the most during rounding. Like this:

declare @T table (grp int, num numeric(10,5))
insert into @T (grp, num) values
(1, 22.41489),
(1, 57.34326),
(1, 20.24185),
(2, 73.50463),
(2, 26.49537)

select
    grp,
    num as original_number,
    round(num, 2) + 
    case when 1 = row_number() over (partition by grp order by num - round(num, 2) desc)
    then (100 - sum(round(num, 2)) over (partition by grp))
    else 0
    end as modified_number  
from @T

Results

1   22.41489    22.42000
1   57.34326    57.34000
1   20.24185    20.24000
2   73.50463    73.50000
2   26.49537    26.50000

SQL get decimal with only 2 places with no round

You could accomplish this via the ROUND() function using the length and precision parameters to truncate your value instead of actually rounding it :

SELECT ROUND(3381.5786, 2, 1)

The second parameter of 2 indicates that the value will be rounded to two decimal places and the third precision parameter will indicate if actual rounding or truncation is performed (non-zero values will truncate instead of round).

Example

You can see an interactive example of this in action here.

How to return a number with two decimal places in SQL Server without it automatically rounding

You can use floor() and integer division:

select floor(8.23897666 * 100) / 100

Or better yet, use round() with a non-0 third argument:

select round(8.23897666, 2, 1)

SQL Rounding to 2 decimals places Issue

This can happen if your decimal-like values are in fact floats. Here is a repro:

DECLARE @test TABLE (line INT, amount FLOAT);
INSERT INTO @test VALUES
(1, 6.525),
(2, 6.524999999999999);

SELECT line, amount, FORMAT(amount, 'G17') AS dotnet_g17_formatted, ROUND(amount, 2) AS amount2
FROM @test

Result:

| line | amount | dotnet_g17_formatted | amount2 |
|------|--------|----------------------|---------|
| 1    | 6.525  | 6.5250000000000004   | 6.53    |
| 2    | 6.525  | 6.5249999999999986   | 6.52    |

You can see that float values are stored as an approximation and displayed as such.

The most appropriate solution is to store financial values as DECIMAL.

---

Related Topics