Fixed Decimal Places

Fixed Decimal Places

Post by jason denn » Sat, 01 Dec 2001 19:25:05



Ok what I'm I doing wrong ;)

I've looked at the other posts on this subject and think I
have done everything right. But why I'm I still getting
decimals such as 10.40 and 23.50 , being returned from my
stored procedure as 10.4 and 23.5 !  Values such as 10.44
are ok, it just seems to be values with a zero at the end.

My table has the field defined as DECIMAL (18,2). When I
view the table in SQL Enterprise manager it also shows the
value without the last zero ! I thought the whole point of
defining a SCALE of 2 was to in effect fix the decimal
places to 2 ?

Even in my Stored Proc I have the following SELECT
statement :

SELECT  rec_id,
        invoice_no,
        CAST(invoice_amt AS NUMERIC(18,2)) AS invoice_amt,
FROM    usr_invoice_tab

...and I still get 10.4 and 23.5 returned !!!

Please help ;)

Regards
Jason.

 
 
 

Fixed Decimal Places

Post by Scott Morri » Sat, 01 Dec 2001 22:01:11


You are confusing presentation with accuracy/storage.  The client
application is responsible for representing the data stored in the
table; it can choose to show more or less decimal places.

> Ok what I'm I doing wrong ;)

> I've looked at the other posts on this subject and think I
> have done everything right. But why I'm I still getting
> decimals such as 10.40 and 23.50 , being returned from my
> stored procedure as 10.4 and 23.5 !  Values such as 10.44
> are ok, it just seems to be values with a zero at the end.

> My table has the field defined as DECIMAL (18,2). When I
> view the table in SQL Enterprise manager it also shows the
> value without the last zero ! I thought the whole point of
> defining a SCALE of 2 was to in effect fix the decimal
> places to 2 ?

> Even in my Stored Proc I have the following SELECT
> statement :

> SELECT  rec_id,
>         invoice_no,
>         CAST(invoice_amt AS NUMERIC(18,2)) AS invoice_amt,
> FROM    usr_invoice_tab

> ...and I still get 10.4 and 23.5 returned !!!

> Please help ;)

> Regards
> Jason.


 
 
 

Fixed Decimal Places

Post by Ben Amad » Sun, 02 Dec 2001 04:14:08


10.4 = 10.40

A columns SCALE property tells SQL Server the Maximum number of places to
the right of the decimal place it can store.  It will not store more places
than defined by SCALE.  So if you divided 10/3 which is 3.3333333333333 ...
and stored the result to a column defined as DECIMAL(18, 2), the stored
result will be 3.33.

As you said, for the number 10.44, SQL Server will store that, but not 10.4.
This is because 10.44 <> 10.4 and 10.44 <> 10.40, etc.  But for the number
10.40, SQL Server stores 10.4.  This is because 10.4 = 10.40.  It doesn't
need to store the last 0 since it doesn't matter (because its the same
value).  Calculators I think work the same way.

To Display 10.40 instead of 10.4, you just need to format the value when you
output it.  For instance converting it to a VarChar and adding a 0 to the
end of it.

Ben


Quote:> Ok what I'm I doing wrong ;)

> I've looked at the other posts on this subject and think I
> have done everything right. But why I'm I still getting
> decimals such as 10.40 and 23.50 , being returned from my
> stored procedure as 10.4 and 23.5 !  Values such as 10.44
> are ok, it just seems to be values with a zero at the end.

> My table has the field defined as DECIMAL (18,2). When I
> view the table in SQL Enterprise manager it also shows the
> value without the last zero ! I thought the whole point of
> defining a SCALE of 2 was to in effect fix the decimal
> places to 2 ?

> Even in my Stored Proc I have the following SELECT
> statement :

> SELECT  rec_id,
> invoice_no,
> CAST(invoice_amt AS NUMERIC(18,2)) AS invoice_amt,
> FROM    usr_invoice_tab

> ...and I still get 10.4 and 23.5 returned !!!

> Please help ;)

> Regards
> Jason.

 
 
 

1. Sqlserver 2000, truncating decimal places in a calculation.....

When doing a calculation, one with variables and one with plain numeric
values, that should return the same result, I get an answer that is
truncated:
*********************************







print ((0.015000*20000) - ((20000*16.666670*0.015000)/100)) /20000
**********************************
The result:
**********************************
0.012499
0.0124999995000000000000
**********************************

Why is this happening? What can I do to fix it?

Best regards,
Bjorn.

2. Do i need to quote a date before insert it in database?

3. Integer data type shown with comma and decimal place

4. database

5. Decimal Places

6. system db locations

7. Calculations & decimal places

8. COPY as non super user

9. decimal places

10. Calculations using decimal places

11. SQL syntax - limiting decimal places in a statement

12. limiting decimal places in float column type

13. 4 decimal places from MONEY output parameter