Posted by Roy Harvey (SQL Server MVP) on 09/24/07 17:50

On Mon, 24 Sep 2007 10:17:15 -0700, ibcarolek <carolek@ix.netcom.com>
wrote:

>We have a field which is decimal (9,2) and another which is decimal
>(9,3). Is there anyway to subtract the two and get a precision 3
>value without changing the first field to 9,3?
>
>For instance, retail value is 9,2, but our costs are at 9,3 due to
>being averaged. To calculate margin (retail-cost), we want that also
>to be 9,3, but a basic subtraction comes out 9,2. You can see we
>don't want to increase retail to be 9,3 (that would look funny), and
>it seems wasteful to store retail twice (one 9,2 for users and one 9,3
>for margin calc)...is there any other way?

I ran the following test using SQL Server 2000 and 2005 but could not
reproduce the behavior you describe.

DECLARE @field1 decimal(9,2)
SET @field1 = 123.45
DECLARE @field2 decimal(9,3)
SET @field2 = 123.456

SELECT @field1, @field2, @field1 - @field2

----------- ----------- -------------
123.45 123.456 -.006

The result always went to three decimal places.

Having said that, you can explicitly convert before performing the
calculation:

SELECT CONVERT(decimal(9,3),@field1) - @field2
SELECT CAST (@field1 as decimal(9,3)) - @field2

Roy Harvey
Beacon Falls, CT

[Back to original message]