Decimal Truncation in division

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 2008 » T-SQL (SS2K8) » Decimal Truncation in division

Decimal Truncation in division

Rate Topic

Display Mode

Topic Options

Author

Message

dutt

Posted Monday, February 22, 2010 12:02 AM

Mr 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

Hariprasad.M

Posted Monday, February 22, 2010 5:50 AM

SSC-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

Paul White

Posted Monday, February 22, 2010 7:49 AM

SSChampion

Group: General Forum Members
Last Login: Yesterday @ 2:43 PM
Points: 10,990, Visits: 10,576

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

Hariprasad.M

Posted Monday, February 22, 2010 10:45 PM

SSC-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

Paul White

Posted Monday, February 22, 2010 10:53 PM

SSChampion

Group: General Forum Members
Last Login: Yesterday @ 2:43 PM
Points: 10,990, Visits: 10,576

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 Wink

Paul White
SQL Server MVP
SQLblog.com
@SQL_Kiwi

Post #870921

« Prev Topic | Next Topic »

Permissions