Prev: Attempt to fetch logical page in database
Next: Log Bad Records
From: Sven Peeters on 29 Jul 2010 09:56
Hi,


I'm a bit dazzled why this statement won't work in T-SQL : Select Round(9.990000, 0)

The error I get is : Arithmetic overflow error converting expression to data type numeric.

The result should be : 10

I've tried this in SQL 2005,2008 and 2008 R2 (Error message if from R2), didn't find anything in BOL either

Select ROUND(19.99, 0) works just fine


Is this a bug or not? We develop accounting software and for us this is a dangerous bug.





Submitted via EggHeadCafe - Software Developer Portal of Choice
Assemblies in Folder Debug Build Checker
http://www.eggheadcafe.com/tutorials/aspnet/d7de5fe1-6155-4e81-96e1-9806fd69d760/assemblies-in-folder-debug-build-checker.aspx
From: Dan on 29 Jul 2010 10:10

"Sven Peeters" <sven.peeters(a)systemat.com> wrote in message
news:201072995556sven.peeters(a)systemat.com...
> Hi,
>
>
> I'm a bit dazzled why this statement won't work in T-SQL : Select
> Round(9.990000, 0)
>
> The error I get is : Arithmetic overflow error converting expression to
> data type numeric.
>
> The result should be : 10
>
> I've tried this in SQL 2005,2008 and 2008 R2 (Error message if from R2),
> didn't find anything in BOL either
>
> Select ROUND(19.99, 0) works just fine
>
>
> Is this a bug or not? We develop accounting software and for us this is a
> dangerous bug.

Odd. I just tried it in Server SQL 2000 SP3 on Windows 2003 SP1, and SQL
Server 2005 SP2 on Windows 2003 SP2, and it returns 10.000000 on both which
is expected. In both cases I used iSQL, so it may be down to connection
properties.

--
Dan

From: Plamen Ratchev on 29 Jul 2010 10:28
This is because 9.990000 is treated as NUMERIC(7, 6) and after the
rounding becomes NUMERIC(8, 6), so it overflows. To fix it you have to
cast explicitly the argument to correct precision and scale:

SELECT ROUND(CAST(9.990000 AS DECIMAL(8, 6)), 0);

Looking at the ROUND documentation in BOL this may be considered a
bug. According to this:

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

The return expression when the argument is NUMERIC(p, s) is
DECIMAL(38, s), so it should have resulted in no error.

--
Plamen Ratchev
http://www.SQLStudio.com
From: Scott Morris on 29 Jul 2010 10:28

"Sven Peeters" <sven.peeters(a)systemat.com> wrote in message
news:201072995556sven.peeters(a)systemat.com...
> Hi,
>
>
> I'm a bit dazzled why this statement won't work in T-SQL : Select
> Round(9.990000, 0)
>
> The error I get is : Arithmetic overflow error converting expression to
> data type numeric.
>
> The result should be : 10
>
> I've tried this in SQL 2005,2008 and 2008 R2 (Error message if from R2),
> didn't find anything in BOL either
>
> Select ROUND(19.99, 0) works just fine
>
>
> Is this a bug or not? We develop accounting software and for us this is a
> dangerous bug.

The answer is very simple and not a bug. What is the datatype of 9.990000?
The following will tell you.

select 9.99000 as xx into testtb
exec sp_help 'testtb'

The moral: If you use literals you must know what datatype the database
engine uses to represent that value. Alternatively, specify the datatype so
there is no possibility of guessing.


From: Erland Sommarskog on 29 Jul 2010 11:12
Scott Morris (bogus(a)bogus.com) writes:
> The answer is very simple and not a bug. What is the datatype of
> 9.990000? The following will tell you.
>
> select 9.99000 as xx into testtb
> exec sp_help 'testtb'

An alternate way to find the same information is this:

declare @x sql_variant
select @x = 9.990000
select sql_variant_property(@x, 'Basetype'),
sql_variant_property(@x, 'Precision'),
sql_variant_property(@x, 'Scale')


More to type, but creates no table.

> The moral: If you use literals you must know what datatype the database
> engine uses to represent that value. Alternatively, specify the
> datatype so there is no possibility of guessing.

What is the datatype of a literal in SQL Server is definitely an trivial
thing, and there is more then one situation where results are not as
expected. What about this:

select power(10.0, -2), power(10.00, -2)

And, Dan... ISQL uses DB-library connects with ARITHABORT and ANSI_WARNINGS
ON, so it lets you get away with it.


--
Erland Sommarskog, SQL Server MVP, esquel(a)sommarskog.se

Links for SQL Server Books Online:
SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx
SQL 2000: http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

 |  Next  |  Last
Pages: 1 2
Prev: Attempt to fetch logical page in database
Next: Log Bad Records