SQL Query not throwing error even if the entered scale value is higher than specified 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.
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:


CAST and CONVERT (Transact-SQL)


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.&lt;key&gt; 
from t1 join t2 
    on t1.&lt;key&gt; = t2.&lt;key&gt; 
where t2.&lt;col&gt; - t1.&lt;col&gt; &lt;&gt; 0