Doubt with Round function

# Doubt with Round function

• Wednesday, February 23, 2011 10:34 AM

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

### All Replies

• Wednesday, February 23, 2011 10:36 AM

Same here (SQL 10.50.1600).

but works in this case:

declare @t as money

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

• Edited by Wednesday, February 23, 2011 10:40 AM
• Proposed As Answer by Wednesday, February 23, 2011 11:01 AM
•
• Wednesday, February 23, 2011 10:39 AM

Hi,

Mine is SQL 10.0.1600.22

Thanks and regards, Rishabh
• Wednesday, February 23, 2011 10:42 AM

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
• Wednesday, February 23, 2011 10:57 AM

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

• Proposed As Answer by Wednesday, February 23, 2011 10:59 AM
• Marked As Answer by Wednesday, February 23, 2011 11:04 AM
•
• Wednesday, February 23, 2011 11:06 AM

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
• Wednesday, February 23, 2011 1:18 PM

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, esquel@sommarskog.se
Links for SQL Server Books Online: SQL 2008, SQL 2005 and  SQL 2000.
(Just click the link you need.)
• Marked As Answer by Thursday, February 24, 2011 3:42 AM
•
• Wednesday, February 23, 2011 3:18 PM

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

• Wednesday, February 23, 2011 11:09 PM

> 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, esquel@sommarskog.se
Links for SQL Server Books Online: SQL 2008, SQL 2005 and  SQL 2000.
(Just click the link you need.)
• Marked As Answer by Thursday, February 24, 2011 3:42 AM
•
• Thursday, February 24, 2011 3:42 AM

Hi,

Thanks Erland..

Thanks and regards, Rishabh
• Thursday, February 24, 2011 9:41 AM

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

• Thursday, February 24, 2011 11:02 PM

> * 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, esquel@sommarskog.se
Links for SQL Server Books Online: SQL 2008, SQL 2005 and  SQL 2000.
(Just click the link you need.)
• Friday, February 25, 2011 8:05 AM

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