Overflow Errors on Converting Big Values to NUMERIC in SQL Server

Q

What Happens When Converting Big Values to NUMERIC Data Types in SQL Server Transact-SQL?

✍: FYIcenter.com

A

If you are converting a numeric expression to a NUMERIC data type and the value is too big for the storage size, you will get an arithmetic overflow error as shown in the following examples:

-- Overflow error on implicit conversion
DECLARE @x FLOAT(24);
DECLARE @y NUMERIC(5,2);
SET @x = 12345.12E+00;
SET @y = @x;
GO
Msg 8115, Level 16, State 6, Line 4
Arithmetic overflow error converting real to data type 
   numeric.

-- Overflow error on explicit conversions
DECLARE @x FLOAT(24);
SET @x = 12345.12E+00;
SELECT CAST(@x AS NUMERIC(5,2));
SELECT CONVERT(NUMERIC(5,2),@x);
GO
Msg 8115, Level 16, State 6, Line 3
Arithmetic overflow error converting real to data type 
   numeric.
Msg 8115, Level 16, State 6, Line 4
Arithmetic overflow error converting real to data type 
   numeric.

 

Mathematical Functions Supported by SQL Server 2005 in SQL Server

Overflow Errors on Converting Big Values to Integers in SQL Server

Numeric Expressions and Functions in SQL Server Transact-SQL

⇑⇑ SQL Server Transact-SQL Tutorials

2017-03-22, 1937🔥, 0💬