Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Decimal Truncation in division


Decimal Truncation in division

Author
Message
dutt
dutt
Mr or Mrs. 500
Mr or Mrs. 500 (504 reputation)Mr or Mrs. 500 (504 reputation)Mr or Mrs. 500 (504 reputation)Mr or Mrs. 500 (504 reputation)Mr or Mrs. 500 (504 reputation)Mr or Mrs. 500 (504 reputation)Mr or Mrs. 500 (504 reputation)Mr or Mrs. 500 (504 reputation)

Group: General Forum Members
Points: 504 Visits: 33
Hi

Look at these SELECT statements.

1. SELECT CAST(1.67574 AS DECIMAL(38,10)) A, CAST(10000 AS DECIMAL(38,10)) B

2. SELECT CAST(1.67574 AS DECIMAL(38,10)) / CAST(10000 AS DECIMAL(38,10)) conv_factor

When first statement is run the result is
A B
1.6757400000 10000.0000000000

But, when the second one runs, the result is

conv_factor
0.000167

Both the numbers are converted into DECIMAL(38,10) before the division, but, why did this truncation occured?
Hariprasad.M
Hariprasad.M
SSC-Enthusiastic
SSC-Enthusiastic (100 reputation)SSC-Enthusiastic (100 reputation)SSC-Enthusiastic (100 reputation)SSC-Enthusiastic (100 reputation)SSC-Enthusiastic (100 reputation)SSC-Enthusiastic (100 reputation)SSC-Enthusiastic (100 reputation)SSC-Enthusiastic (100 reputation)

Group: General Forum Members
Points: 100 Visits: 75
Hi,
Try this,

SELECT CAST(CAST(1.67574 AS DECIMAL(38,10)) / CAST(10000 AS DECIMAL(38,10)) AS DECIMAL(38,10)) conv_factor

SQL server taking a default scale, you need to explicitly specify the precision and scale.

regards
Hari
Paul White
Paul White
SSChampion
SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)

Group: General Forum Members
Points: 10338 Visits: 11350
Duplicate thread.

I posted a comprehensive answer here:

http://www.sqlservercentral.com/Forums/Topic870098-338-1.aspx



Paul White
SQLPerformance.com
SQLblog.com
@SQL_Kiwi
Hariprasad.M
Hariprasad.M
SSC-Enthusiastic
SSC-Enthusiastic (100 reputation)SSC-Enthusiastic (100 reputation)SSC-Enthusiastic (100 reputation)SSC-Enthusiastic (100 reputation)SSC-Enthusiastic (100 reputation)SSC-Enthusiastic (100 reputation)SSC-Enthusiastic (100 reputation)SSC-Enthusiastic (100 reputation)

Group: General Forum Members
Points: 100 Visits: 75
Thanks paul, that was good one.

regards
Hari
Paul White
Paul White
SSChampion
SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)

Group: General Forum Members
Points: 10338 Visits: 11350
Hariprasad.M (2/22/2010)
Thanks paul, that was good one.

No worries, Hari - I enjoyed writing the reply. It would be cool if you would just post your questions in one forum from now on though ;-)



Paul White
SQLPerformance.com
SQLblog.com
@SQL_Kiwi
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search