General MySQL Oracle SQL Server

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, 4014🔥, 0💬

General MySQL Oracle SQL Server