FIX: Dividing 0 by a Negative Decimal or Numeric Number Results in -0
This article was previously published under Q285544
This article has been archived. It is offered "as is" and will no longer be updated.
BUG #: 203148 (SHILOH_bugs)
When using decimal or numeric data types, if you divide 0 by a negative number, a negative 0 (-0) is returned.
To resolve this problem, obtain the latest service pack for SQL Server 2000. For additional information, click the following article number to view the article in theMicrosoft Knowledge Base:
INF: How to Obtain the Latest SQL Server 2000 Service Pack
Check for 0 before you perform the division and avoid the division if 0 is involved; for example:
declare @v1 dec(10, 2), @v2 dec(2, 1), @v3 dec (10,2) -- @v3 is the result of @v1/@v2select @v1 = 0.0, @v2 = -1.0if @v1 = 0 select @v3 = 0.0else select @v3 = @v1 / @v2
Microsoft has confirmed that this is a problem in SQL Server 2000. This problem was first corrected in SQL Server 2000 Service Pack 1.
SQL Server does not consider the computed value (-0) equal to 0, so the comparison of the computed value (-0) with 0 fails. For example, the following code
declare @v1 dec(10, 2), @v2 dec(2, 1) select @v1 = 0.0, @v2 = -1.0 if @v1 / @v2 = 0 print 'equals zero' else print 'not equal zero'
Gives the following result:
For additional information about related bugs in SQL Server 7.0, click the article number below to view the article in the Microsoft Knowledge Base:
FIX: Mathematical Operations on Numeric or Decimal Columns with Negative Numbers May Return Unexpected Results
Article ID: 285544 - Last Review: 01/16/2015 21:25:15 - Revision: 3.2
Microsoft SQL Server 2000 Standard Edition
- kbnosurvey kbarchive kbbug kbfix kbsqlserv2000sp1fix KB285544