SQL Server Decimal(30,10) Losing Last 2 Decimals

Sql Server Decimal(30,10) losing last 2 decimals

The maximum precision allowed in SQL Server is 38. You are using Decimal(30,10). The max value is 99,999,999,999,999,999,999.9999999999 if you divide this number by 0.000000001, you will end up with an even bigger number, so the resulting data type must be able to accommodate it. This causes you to lose some precision.

Change your original data types to Decimal(20,10) and this problem does not occur.

For full rules regarding data types (and how they are affected by math operations):

Full rules here

Why are my decimal values being rounded to integers in SQL insertions?

You did not define a scale/precision for your decimal. If you want 3 digits after the decimal you should define it as DECIMAL(9,3) which would give you 6 places before the decimal and a decimal of up to 3 places. You need to analyze the expected data and based on what you expect specify the correct precision and scale for your column definition.

CREATE TABLE tmp(
    id int NOT NULL IDENTITY(1,1)PRIMARY KEY,
    toleranceRegion DECIMAL(9,3)
)

See the Sql Server documentation for decimal here.

Truncate (not round) decimal places in SQL Server

select round(123.456, 2, 1)

Choosing the appropriate precision for decimal(x,y)

The main reason to use a smaller data precision, despite using the same amount of storage space, is to convey meaning to future users of the system. Which is also why it's important to use appropriate data types - e.g. DECIMAL(15,4) for numbers, MONEY for money.

Remove decimal values using SQL query

Since all your values end with ".00", there will be no rounding issues, this will work

SELECT CAST(columnname AS INT) AS columnname from tablename

to update

UPDATE tablename
SET columnname = CAST(columnname AS INT)
WHERE .....

Rounding Decimals in SQL

Sounds like you are using a float to store the value. This can be reproed with the below:

SELECT d,f,r,
       ROUND(V.d,2) AS dr,
       ROUND(V.f,2) AS fr,
       ROUND(V.r,2) AS vr
FROM (VALUES(CONVERT(decimal(5,3),13.395),CONVERT(float,13.395),CONVERT(real,13.395)))V(d,f,r);

Note that fr has a value of 13.39.

The simple answer is stop storing values as a float unless you are happy with such rounding issues. float is not a precise value. If, however, you CONVERT the value to an appropriate decimal scale and size first (so a scale of 3), you don't suffer the problem:

SELECT ROUND(CONVERT(decimal(5,3),V.f),2) fr2
FROM (VALUES(CONVERT(decimal(5,3),13.395),CONVERT(float,13.395),CONVERT(real,13.395)))V(d,f,r);

The reason you get the wrong answer is because when you CONVERT the value to a decimal(30,2) you've already lost the accuracy, as CONVERT(decimal(30,2),V.f) returns 13.39.

db<>fiddle

---

Related Topics