Rounding to 2 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

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

How to display two digits after decimal point in SQL Server

select cast(your_float_column as decimal(10,2))
from your_table

decimal(10,2) means you can have a decimal number with a maximal total precision of 10 digits. 2 of them after the decimal point and 8 before.

The biggest possible number would be 99999999.99

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.

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)

How do I get 2 place decimal without rounding off in MySQL?

TRUNCATE()

MySQL TRUNCATE() returns a number after truncated to certain decimal places. The number and the number of decimal places are specified as arguments of the TRUNCATE function.

  SELECT TRUNCATE(DateColumn,n) from TableName;

example:

  SELECT TRUNCATE(25.21512,2) from dual;

result:

-> 25.21

---

Related Topics