Collections:
Overflow and Rounding on NUMERIC Values in SQL Server Transact-SQL
How Extra Digits Are Handled with NUMERIC Data Type Literals in SQL Server Transact-SQL?
✍: FYIcenter.com
Exact numeric data types defined with NUMERIC(p,s) has two limits defined by two parameters: p (precision) and s (scale):
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, 4585🔥, 0💬
Popular Posts:
What Happens If the UPDATE Subquery Returns Multiple Rows in MySQL? If a subquery is used in a UPDAT...
What Are the Underflow and Overflow Behaviors on FLOAT Literals in SQL Server Transact-SQL? If you e...
How To Replace Given Values with NULL using NULLIF() in SQL Server Transact-SQL? Sometime you want t...
How to obtain the number of rows found by the last SELECT statement using the FOUND_ROWS() function?...
How To Replace Given Values with NULL using NULLIF() in SQL Server Transact-SQL? Sometime you want t...