Doubt with Round function

Question

HI,

I was having a look at the tutorial of microsoft about the round function.

http://msdn.microsoft.com/en-us/library/ms175003.aspx

the value of ROUND(748.58, -3) was given as 1000.00

but when i run the same select statement on my server it returned error as

Arithmetic overflow error converting expression to data type numeric.

Can somebody explain me why this is happening

---

Thanks and regards, Rishabh

Answer 1

Same here (SQL 10.50.1600).

but works in this case:

declare @t as money

set @t = 748.48
select ROUND(@t, -3)

 

Answer 2

Hi,

Mine is SQL 10.0.1600.22

---

Thanks and regards, Rishabh

Answer 3

Hi,

One More thing when taken inside the variable its working fine

declare @n numeric(7,2)
set @n=748.58
select @n
select ROUND(@n,-3)

 

 

---

Thanks and regards, Rishabh

Answer 4

This is because 748.58 is treated as NUMERIC(5, 2)

 

/*

select 748.58 as col into tab

exec sp_help 'tab'

*/

 

and after the rounding becomes NUMERIC(9, 5), so it overflows. To fix it you have to cast the argument to correct precision and scale.

 

According to this:

http://msdn.microsoft.com/en-us/library/ms175003.aspx

 

Answer 5

Hello,

When you use number without specifying the type bay cast, SQL Server uses the smallest possible. In this case it's numeric(5, 2). Rounded with -4 results in numeric(6, 2), that's to large for num(5, 2).

You can check the result type with following statement, then edit _dummy table in SSMS:

SELECT 548.58 AS Val

INTO _dummy

 

---

Olaf Helper
* cogito ergo sum * errare humanum est * quote erat demonstrandum *
Wenn ich denke, ist das ein Fehler und das beweise ich täglich
Blog Xing

Answer 6

As other have explained, the implicit data type of the literal 748.58 is numeric(5,2), why 1000 causes an overflow.

But how could they put this example in the topic for round()? Because there was a bug, and in SQL 2005 and earlier round would actually return 1000 in this case, as well as in this case:

declare @c numeric(5,2)
select @c = 748.58
select ROUND(@c, -3)

---

Erland Sommarskog, SQL Server MVP, [e-mail]
Links for SQL Server Books Online: SQL 2008, SQL 2005 and  SQL 2000.
(Just click the link you need.)

Answer 7

Hello Olaf,

I agree to your explaination. However, not every TSQL math function tries to return its result in the argument's datatype. For example, PRINT EXP(74.58) returns not an overflow but 2.45291e+032. Not very consequent, in my eyes.

Klaus

Answer 8

> I agree to your explaination. However, not every TSQL math function tries to return its result in the argument's datatype. For example, PRINT EXP(74.58) returns not an overflow but 2.45291e+032. Not very consequent, in my eyes.

The return type for each function is described in Books Online. It does make sense for ROUND to return type. It does not make sense for EXP.

I've reported the doc error for round here: https://connect.microsoft.com/SQLServer/feedback/details/646516/bad-example-for-round

It seems that I was guilty to the bug report that changed the behaviour as well: https://connect.microsoft.com/SQLServer/feedback/details/288555/out-of-band-decimal-value-is-returned-incorrectly#details

---

Erland Sommarskog, SQL Server MVP, [e-mail]
Links for SQL Server Books Online: SQL 2008, SQL 2005 and  SQL 2000.
(Just click the link you need.)

Answer 9

Hi,

Thanks Erland..

---

Thanks and regards, Rishabh

Answer 10

Hello Erland,

I don't want to start a philosophical discussion, but let me remark that:

• the datatype-specific behavior of math functions in the SSMS online documentation (at least in the German version) isn't described in a satisfying way, see e.g. POWER() or RADIANS().
• I could imagine a "smarter" way of handling NUMERIC datatypes by math functions. Why shouldn't ROUND(748.54, -3) avoid an overflow and return a numeric(6,2) result?
• There seems to be two groups of math function. The first group acts like ROUND(), POWER(), RADIANS(), ... and the second one like EXP(), SQRT(), SIN() ... It may be clear that EXP() has to belong to the second group. However, for me it's not clear why POWER() belongs to group 1 and SQRT() to group 2. I'm nearly sure there are e.g. financial formulas applying SQRT() to amounts of money.

This is why I'm not happy with the current situation. I fear it's a permanent source of programming errors, in particular if a database programmer switches between TSQL and other languages.

Klaus

Answer 11

> * the datatype-specific behavior of math functions in the SSMS online documentation (at least in the German version) isn't described in a satisfying way, see e.g. POWER() or RADIANS().

I recall that I once tried "select power(10.0, -2)" and I could not understand the result. You really need "select power(10.00, -2)".

In Books Online for SQL 2000, it is said that the return type is "Same as numeric_expression.", which agrees with what I said above.

But in Books Online for SQL 2008, the argument is called float_expression which is incorrect. The expression can be any numeric data type.

In any case, for power() it does make some sense - if you say power(2, 16), you probably want 65536, 6.5536+E4.

On the other hand, the fact that radians() does not always return float is just crazy.

And, of course, the implicit data type of numeric literals certainly adds to the confusion.

---

Erland Sommarskog, SQL Server MVP, [e-mail]
Links for SQL Server Books Online: SQL 2008, SQL 2005 and  SQL 2000.
(Just click the link you need.)

Answer 12

Hello Erland,

maybe we can expect some future improvements in the math part of TSQL:

Von Microsoft am 12.01.2011 um 16:20 bereitgestellt

Hi,
Thanks for your feedback. We understand that there are inconsistencies in the implementation of existing math built-ins. Unfortunately, due to backward compatibility reasons we cannot change the existing behavior of these built-ins. We will take this into consideration for any new math built-ins that we introduce. Also, there is revision to IEEE 754 which defines new binary floating point numbers which can impact how we should perform calculations in the future. So we will take a look at all of these in the future. We have different feedback items tracking those and I will close this one for now.

--
Umachandar, SQL Programmability Team

Regards,

Klaus