5

I need to capture numeric values without truncation. But the output value is getting truncated even though I specified the Scale as '2'.

Column 'A' datatype - numeric(5,2)

Case 1:

Entered value: 123.55689335218777665555777778899995544

Output : 123.56

Case 2:

Entered value: 1234.55689335218777665555777778899995544

Output: Arithmetic overflow error converting varchar to data type numeric.

My question is why I am not receiving any error when the scale is higher than '2' but I am getting error when the precision is higher than 3.

I am facing this issue in SSIS Data Conversion task. I need to capture the rows which are truncated in another table. Simplified this question in this way for obvious reasons.

1 Answer 1

4

The biggest number you can fit into numeric(5,2) is 999.99. Therefore 123.556... is fine, but 1234.556... is too big.

You can find the rules for truncation, rounding, and errors during implicit conversions in the documentation:

I don't know SQL-server that well, so I don’t know if you can configure the behaviour. One idea is to load the data into a table with say numeric(7,4). You can then check which values are truncated by a query similar to:

select t1.<key> 
from t1 join t2 
    on t1.<key> = t2.<key> 
where t2.<col> - t1.<col> <> 0
0

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.