Home >> FAQs/Tutorials >> SQL Server FAQ
SQL Server FAQ  Overflow Errors and Rounding on NUMERIC Literals
By: FYIcenter.com
(Continued from previous topic...)
How Extra Digits Are Handled with NUMERIC Data Type Literals?
Exact numeric data types defined with NUMERIC(p,s) has two limits defined by two parameters:
p (precision) and s (scale):
 Maximum number of digits of the integer part (digits before the decimal point) is defined as ps.
If this limit is passed, SQL Server will give you an arithmetic overflow error.
 Maximum number of digits of the decimal part (digits after the decimal point) is defined as s.
If this limit is passed, SQL Server will perform a round operation.
The tutorial exercise below gives an example of arithmetic overflow errors and rounding operations.
 Exact numeric value
DECLARE @x NUMERIC(9,2);  NUMERIC(p,s)
SET @x = 1234567.12;
SELECT @x;
GO
1234567.12
 Overflow error: ps limit passed
DECLARE @x NUMERIC(9,2);  NUMERIC(p,s)
SET @x = 123456789.12;
GO
Msg 8115, Level 16, State 8, Line 2
Arithmetic overflow error converting numeric to data type
numeric.
 Rounding on extra decimal digits: s limit passed
DECLARE @x NUMERIC(9,2);  NUMERIC(p,s)
SET @x = 1234567.12345;
SELECT @x;
GO
1234567.12
(Continued on next topic...)
 What Is a Constant or Literal?
 How To Write Character String Constants or Literals?
 What Is a Collation?
 How To Specify the Collation for a Character Data Type?
 What Happens If Strings Are Casted into Wrong Code Pages?
 How To Find Out What Is the Default Collation in a Database?
 How Fixed Length Strings Are Truncated and Padded?
 How To Enter Unicode Character String Literals?
 How To Enter Binary String Literals?
 How To Enter Date and Time Literals?
 Why I Can Not Enter 0.001 Second in Date and Time Literals?
 What Happens If DateOnly Values Are Provided as Date and Time Literals?
 What Happens If TimeOnly Values Are Provided as Date and Time Literals?
 What Are OutofRange Errors with Date and Time Literals?
 What Happens If an Integer Is Too Big for INT Date Type?
 How Extra Digits Are Handled with NUMERIC Data Type Literals?
 How REAL and FLOAT Literal Values Are Rounded?
 What Are the Underflow and Overflow Behaviors on FLOAT Literals?
