Recent PostsRecent Posts Popular TopicsPopular Topics
 Home Search Members Calendar Who's On

 Decimal Truncation in division Rate Topic Display Mode Topic Options
Author
 Message
 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
 HiLook at these SELECT statements.1. SELECT CAST(1.67574 AS DECIMAL(38,10)) A, CAST(10000 AS DECIMAL(38,10)) B2. SELECT CAST(1.67574 AS DECIMAL(38,10)) / CAST(10000 AS DECIMAL(38,10)) conv_factorWhen first statement is run the result isA B1.6757400000 10000.0000000000But, when the second one runs, the result is conv_factor0.000167Both 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 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_factorSQL server taking a default scale, you need to explicitly specify the precision and scale.regardsHari
Post #870266
 Posted Monday, February 22, 2010 7:49 AM
 SSChampion Group: General Forum Members Last Login: Yesterday @ 2:19 PM Points: 11,052, Visits: 10,817
Post #870361
 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. regardsHari
Post #870919
 Posted Monday, February 22, 2010 10:53 PM
 SSChampion Group: General Forum Members Last Login: Yesterday @ 2:19 PM Points: 11,052, Visits: 10,817
 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 WhiteSQL Server MVPSQLblog.com@SQL_Kiwi
Post #870921

 Permissions