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

**⇒** Approximate Numeric Data Types in SQL Server Transact-SQL

**⇐** Overflow Errors with INT Values in SQL Server Transact-SQL

2017-04-19, 2352🔥, 0💬

Popular Posts:

How Fixed Length Strings Are Truncated and Padded in SQL Server Transact-SQL? When the length of the...

How To Run SQL Commands in SQL*Plus in Oracle? If you want to run a SQL command in SQL*Plus, you nee...

What Happens If the Imported Table Already Exists in Oracle? If the import process tries to import a...

How To Download Oracle Database 10g XE in Oracle? If you want to download a copy of Oracle Database ...

What Is SQL*Plus in Oracle? SQL*Plus is an interactive and batch query tool that is installed with e...