Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Decimal Truncation in division Expand / Collapse
Author
Message
Posted Monday, February 22, 2010 12:02 AM
Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Friday, August 27, 2010 4:39 AM
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?
Post #870096
Posted Monday, February 22, 2010 5:50 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Friday, April 12, 2013 7:32 AM
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
Post #870266
Posted Monday, February 22, 2010 7:49 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 8:58 PM
Points: 11,168, Visits: 10,930
Duplicate thread.

I posted a comprehensive answer here:

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




Paul White
SQL Server MVP
SQLblog.com
@SQL_Kiwi
Post #870361
Posted Monday, February 22, 2010 10:45 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Friday, April 12, 2013 7:32 AM
Points: 100, Visits: 75
Thanks paul, that was good one.

regards
Hari
Post #870919
Posted Monday, February 22, 2010 10:53 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 8:58 PM
Points: 11,168, Visits: 10,930
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
SQL Server MVP
SQLblog.com
@SQL_Kiwi
Post #870921
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse