467,074 Members | 1,102 Online
Bytes | Developer Community
Ask Question

Home New Posts Topics Members FAQ

Post your question to a community of 467,074 developers. It's quick & easy.

subtraction of different precision values

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?

Sep 24 '07 #1
  • viewed: 6439
Share:
2 Replies
On Mon, 24 Sep 2007 10:17:15 -0700, ibcarolek 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?
Hi ibcarolek,

Can you post a repro that demonstrates the issue? If I subtract a
decimal(9,3) from a decimal(9,2), the result has three decimal places,
as demonstrated by the repro below. You are obviously doing something in
a different way than I am - I need to know what before I can help you
solve the issue.

DECLARE @Retail decimal(9,2), @Costs decimal(9,3);
SET @Retail = 12.04;
SET @Costs = 9.833;
SELECT @Retail - @Costs;

Result:

2.207
--
Hugo Kornelis, SQL Server MVP
My SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis
Sep 24 '07 #2
On Mon, 24 Sep 2007 10:17:15 -0700, ibcarolek <ca*****@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
Sep 24 '07 #3

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

24 posts views Thread by Philipp | last post: by
5 posts views Thread by DAVID SCHULMAN | last post: by
5 posts views Thread by bruce.james.lee@gmail.com | last post: by
15 posts views Thread by michael.mcgarry@gmail.com | last post: by
17 posts views Thread by spooler123@gmail.com | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.