Getting undesirable trailing zeros after casting to decimal

• david.dartnell

  Ten Centuries

  Points: 1276

  More actions

  September 7, 2014 at 8:00 pm

  #397311

  Hi Everyone,

  The following line of SQL returns the first screen shot below.

  ((T0.TotalSales - ISNULL(T1.TotalSales, 0)) - (T0.StockValue - ISNULL(T1.StockValue, 0))) / (T0.StockValue - ISNULL(T1.StockValue, 0)) * 100 AS 'Gross Profit %'

  

  I now want to limit the decimal places to two (2), and one might think that using a CAST operation here is the solution, as follows.

  CAST(((T0.TotalSales - ISNULL(T1.TotalSales, 0)) - (T0.StockValue - ISNULL(T1.StockValue, 0))) / (T0.StockValue - ISNULL(T1.StockValue, 0)) * 100 AS decimal(15,2)) AS 'Gross Profit %'

  However in actuality I am still seeing too many decimal places, but the extra ones have changed to ZEROS!

  

  Ideally I would like to have no extra trailing zeros. If you have any suggestions regarding how to achieve this can you please let me know?

  Kind Regards,

  Davo

• mickyT

  SSChampion

  Points: 10360

  More actions

  September 7, 2014 at 8:34 pm

  #1743752

  Hi

  Not sure why you are getting the trailing 0's, unless there is something else causing it to recast after that.

  Taking your statements and replacing the values why some hard coded values the following DECLARE @ts1 DECIMAL(38,15) = 7895.2154

  DECLARE @ts2 DECIMAL(38,15) = 6546.243

  DECLARE @tv1 DECIMAL(38,15) = 6874.2343

  DECLARE @tv2 DECIMAL(38,15) = 5842.2343

  DECLARE @test-2 DECIMAL(38,15) = 0.0

  SELECT ((@ts1 - ISNULL(@ts2, 0)) - (@tv1 - ISNULL(@tv2, 0))) / (@tv1 - ISNULL(@tv2, 0)) * 100 AS 'Gross Profit %',

  CAST(((@ts1 - ISNULL(@ts2, 0)) - (@tv1 - ISNULL(@tv2, 0))) / (@tv1 - ISNULL(@tv2, 0)) * 100 AS DECIMAL(15,2)) AS 'Gross Profit %',

  CAST(((@ts1 - ISNULL(@ts2, 0)) - (@tv1 - ISNULL(@tv2, 0))) / (@tv1 - ISNULL(@tv2, 0)) * 100 AS DECIMAL(15,2)) + @test-2 AS 'Gross Profit %'

  Results inGross Profit % Gross Profit % Gross Profit %

  --------------------------------------- --------------------------------------- ---------------------------------------

  30.714300 30.71 30.710000000000000

• Sean Pearce

  SSCoach

  Points: 15999

  More actions

  September 12, 2014 at 6:40 am

  #1745062

  You have only shared a small piece of your code, so we need to guess.

  Is your code selecting into a table? i.e. Maybe the following code example will show what I mean.

  CREATE TABLE #Test (Value DECIMAL(15, 12));

  INSERT INTO #Test VALUES (123.759815009);

  SELECT * FROM #Test;

  DROP TABLE #Test;

  GO

  CREATE TABLE #Test (Value DECIMAL(15, 12));

  INSERT INTO #Test VALUES (CAST(123.759815009 AS DECIMAL(10, 2)));

  SELECT * FROM #Test;

  DROP TABLE #Test;

  GO

  CREATE TABLE #Test (Value DECIMAL(10, 2));

  INSERT INTO #Test VALUES (123.759815009);

  SELECT * FROM #Test;

  DROP TABLE #Test;

  GO

  

  The SQL Guy @ blogspot[/url]

  @SeanPearceSQL

  About Me[/url]