round function

Can anyone reproduce this with sql 2005?
SELECT ROUND(95.99, -1)
returns:
An error occurred while executing batch. Error message is: Arithmetic
Overflow.
if you try it with anything less than 95 it works. It errors out any
time it is supposed to round up to a number with 1 more digit (i.e. 95
to 100, 996 to 1000).
This worked in sql 2000.
The fix is "select convert(float,round(95.99, -1)"
Very strange.returns 100 for me on
Microsoft SQL Server 2005 - 9.00.2040.00 (Intel X86)
Mar 13 2006 11:20:51
Copyright (c) 1988-2005 Microsoft Corporation
Developer Edition on Windows NT 5.1 (Build 2600: Service Pack 2)
Hilary Cotter
Director of Text Mining and Database Strategy
RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.
This posting is my own and doesn't necessarily represent RelevantNoise's
positions, strategies or opinions.
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
<clemlau@.yahoo.com> wrote in message
news:1157591318.990113.257650@.h48g2000cwc.googlegroups.com...
> Can anyone reproduce this with sql 2005?
> SELECT ROUND(95.99, -1)
> returns:
> An error occurred while executing batch. Error message is: Arithmetic
> Overflow.
> if you try it with anything less than 95 it works. It errors out any
> time it is supposed to round up to a number with 1 more digit (i.e. 95
> to 100, 996 to 1000).
> This worked in sql 2000.
> The fix is "select convert(float,round(95.99, -1)"
> Very strange.
>|||Returns 100.00 for me on:
Microsoft SQL Server 2000 - 8.00.2039 (Intel X86)
May 3 2005 23:18:38
Copyright (c) 1988-2003 Microsoft Corporation
Developer Edition on Windows NT 5.1 (Build 2600: Service Pack 2)
--
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
<clemlau@.yahoo.com> wrote in message
news:1157591318.990113.257650@.h48g2000cwc.googlegroups.com...
> Can anyone reproduce this with sql 2005?
> SELECT ROUND(95.99, -1)
> returns:
> An error occurred while executing batch. Error message is: Arithmetic
> Overflow.
> if you try it with anything less than 95 it works. It errors out any
> time it is supposed to round up to a number with 1 more digit (i.e. 95
> to 100, 996 to 1000).
> This worked in sql 2000.
> The fix is "select convert(float,round(95.99, -1)"
> Very strange.
>|||Even I get an error
An error occurred while executing batch. Error message is: Arithmetic
Overflow.
My @.@.version returns
Microsoft SQL Server 2005 - 9.00.2047.00 (Intel X86)
Apr 14 2006 01:12:25
Copyright (c) 1988-2005 Microsoft Corporation
Enterprise Evaluation Edition on Windows NT 5.1 (Build 2600: Service Pack 2)
"clemlau@.yahoo.com" wrote:
> Can anyone reproduce this with sql 2005?
> SELECT ROUND(95.99, -1)
> returns:
> An error occurred while executing batch. Error message is: Arithmetic
> Overflow.
> if you try it with anything less than 95 it works. It errors out any
> time it is supposed to round up to a number with 1 more digit (i.e. 95
> to 100, 996 to 1000).
> This worked in sql 2000.
> The fix is "select convert(float,round(95.99, -1)"
> Very strange.
>|||Anything from ROUND(95.99, -1) to ROUND(99.99, -1) gives an error. Strange!
"clemlau@.yahoo.com" wrote:
> Can anyone reproduce this with sql 2005?
> SELECT ROUND(95.99, -1)
> returns:
> An error occurred while executing batch. Error message is: Arithmetic
> Overflow.
> if you try it with anything less than 95 it works. It errors out any
> time it is supposed to round up to a number with 1 more digit (i.e. 95
> to 100, 996 to 1000).
> This worked in sql 2000.
> The fix is "select convert(float,round(95.99, -1)"
> Very strange.
>|||I get the error
An error occurred while executing batch. Error message is: Arithmetic
Overflow.
Here is the version number
Microsoft SQL Server 2005 - 9.00.2047.00 (X64)
Apr 14 2006 01:11:53
Copyright (c) 1988-2005 Microsoft Corporation
Enterprise Edition (64-bit) on Windows NT 5.2 (Build 3790: Service Pack 1)
So it works on 32 bit, but does not work 64 bit.
Hilary Cotter
Director of Text Mining and Database Strategy
RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.
This posting is my own and doesn't necessarily represent RelevantNoise's
positions, strategies or opinions.
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
<clemlau@.yahoo.com> wrote in message
news:1157591318.990113.257650@.h48g2000cwc.googlegroups.com...
> Can anyone reproduce this with sql 2005?
> SELECT ROUND(95.99, -1)
> returns:
> An error occurred while executing batch. Error message is: Arithmetic
> Overflow.
> if you try it with anything less than 95 it works. It errors out any
> time it is supposed to round up to a number with 1 more digit (i.e. 95
> to 100, 996 to 1000).
> This worked in sql 2000.
> The fix is "select convert(float,round(95.99, -1)"
> Very strange.
>|||On Thu, 7 Sep 2006 09:11:22 -0400, Hilary Cotter wrote:
>I get the error
>An error occurred while executing batch. Error message is: Arithmetic
>Overflow.
>
>Here is the version number
>Microsoft SQL Server 2005 - 9.00.2047.00 (X64)
>Apr 14 2006 01:11:53
>Copyright (c) 1988-2005 Microsoft Corporation
>Enterprise Edition (64-bit) on Windows NT 5.2 (Build 3790: Service Pack 1)
>
>So it works on 32 bit, but does not work 64 bit.
Hi Hilary,
It doesn't work on all 32-bit systems, though.
SELECT @.@.VERSION
SELECT ROUND(95.99, -1)
On SQL-2005:
Microsoft SQL Server 2005 - 9.00.2153.00 (Intel X86)
May 8 2006 22:41:28
Copyright (c) 1988-2005 Microsoft Corporation
Developer Edition on Windows NT 5.1 (Build 2600: Service Pack 2)
An error occurred while executing batch. Error message is: Rekenkundige
overloop.
On SQL-2000:
Microsoft SQL Server 2000 - 8.00.2187 (Intel X86)
Mar 9 2006 11:38:51
Copyright (c) 1988-2003 Microsoft Corporation
Developer Edition on Windows NT 5.1 (Build 2600: Service Pack 2)
An error occurred while executing batch. Error message is: Rekenkundige
overloop.
Hugo Kornelis, SQL Server MVP