Collections:

Overflow and Rounding on NUMERIC Values in SQL Server Transact-SQL

Q

How Extra Digits Are Handled with NUMERIC Data Type Literals in SQL Server Transact-SQL?

✍: FYIcenter.com

A

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 p-s. 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; ---------------------------- 1234567.12 -- Overflow error: p-s limit passed DECLARE @x NUMERIC(9,2); -- NUMERIC(p,s) SET @x = 123456789.12; ---------------------------- 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; ---------------------------- 1234567.12

2017-04-19, 837👍, 0💬

Popular Posts:

How to put statements into a statement block in SQL Server Transact-SQL? You can put statements into...

How To Create a View on an Existing Table in SQL Server? If you want to a view on an existing table,...

What are DDL (Data Definition Language) statements for tables in SQL Server? DDL (Data Definition La...

How To Calculate DATETIME Value Differences Using the DATEDIFF() Function in SQL Server Transact-SQL...

How To Calculate Age in Days, Hours and Minutes in SQL Server Transact-SQL? On many Web sites, news ...