Home >> FAQs/Tutorials >> SQL Server FAQ

SQL Server FAQ - Overflow Errors on Converting Big Values to NUMERIC

By: FYIcenter.com

(Continued from previous topic...)

What Happens When Converting Big Values to NUMERIC Data Types?

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.

(Continued on next topic...)

1. What Is an Expression?
2. What Are Arithmetic Operators?
3. What Happens to an Arithmetic Operation with Two Different Data Types?
4. How To Convert a Numeric Expression from One Data Type to Another?
5. How To Convert Numeric Expression Data Types by Assignment Operations?
6. How To Convert Numeric Expression Data Types using the CAST() Function?
7. How To Convert Numeric Expression Data Types using the CONVERT() Function?
8. How To Convert Character Strings into Numeric Values?
9. What Happens When Converting Big Values to Integers?
10. What Happens When Converting Big Values to NUMERIC Data Types?
11. What Are the Mathematical Functions Supported by SQL Server 2005?
12. How To Convert Numeric Values to Integers?
13. How To Round a Numeric Value To a Specific Precision?
14. How To Generate Random Numbers with the RAND() Function?