471,337 Members | 795 Online
Bytes | Software Development & Data Engineering Community
Post +

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 471,337 software developers and data experts.

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
2 6847
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 | last post: by
15 posts views Thread by michael.mcgarry | last post: by
17 posts views Thread by spooler123 | last post: by
reply views Thread by rosydwin | last post: by

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.