decimal or money

• repent_kog_is_near

  SSCertifiable

  Points: 7278

  More actions

  November 2, 2009 at 9:25 pm

  #1074097

  Paul White

  The Blog you mentioned says that you can avoid FP by using decimal. That is exactly Gus says is not possible to avoid

  Dan

• jesse.brandon

  Newbie

  Points: 9

  More actions

  September 30, 2010 at 6:56 pm

  #1229979

  repent_kog_is_near (10/28/2009)

  ---

  DECLARE

  @D1 DECIMAL,@D2 DECIMAL ,@D3 DECIMAL(30,6),

  @N1 NUMERIC , @N2 NUMERIC ,@N3 NUMERIC,

  @M1 MONEY,@M2 MONEY ,@M3 MONEY,

  @F1 FLOAT , @F2 FLOAT ,@F3 FLOAT

  --Random Numbers for Multiplication

  SELECT @M1 = 5244.11,@M2 = 649164.029595,@M3 = 57234.22,@F1 = 5244.11,@F2 = 649164.029595,@F3 = 57234.22,@D1 = 5244.11,@D2 = 649164.029595,@D3 = 57234.22,@N1 = 5244.11,@N2 = 649164.029595,@N3 = 57234.22

  :exclamation: Your rounding the values prior to multiplying.

  5244 * 649164 * 57234.22 is not supposed to equal 5244.11 * 649164.029595 * 57234.22

  I decided to dig deeper into what you attempted to present, but failed to see it. The only difference i see is between FLOAT and DECIMAL-NUMERIC-MONEY and the precision level that your looking to record. I see no reason not to use MONEY data types. For items like "rates" or % use DECIMAL's.

  DECLARE

  @D1 DECIMAL(30,6),@D2 DECIMAL (30,6),@D3 DECIMAL(30,6),

  @N1 NUMERIC(30,6), @N2 NUMERIC (30,6) ,@N3 NUMERIC (30,6),

  @M1 MONEY,@M2 MONEY ,@M3 MONEY,

  @F1 FLOAT , @F2 FLOAT ,@F3 FLOAT,

  @R1 DECIMAL(30,6)

  --Random Numbers for Multiplication

  SELECT @M1 = 5244.9999,@M2 = 649164.0999,@M3 = 57234.9999,

  @F1 = 5244.9999,@F2 = 649164.0999,@F3 = 57234.9999,

  @D1 = 5244.9999,@D2 = 649164.0999,@D3 = 57234.9999,

  @N1 = 5244.9999,@N2 = 649164.0999,@N3 = 57234.9999,

  @R1 = .00125

  --Multiply just each datatype with its own type

  SELECT

  @M1 * @M2 * @M3 As "Money 123",

  @M2 * @M3 * @M1 AS "Money 231",

  @M3 * @M1 * @M2 AS "Money 312",

  @M1 * @M2 AS "Money 12",

  @M2 * @M3 AS "Money 23"

  SELECT

  @D1 * @D2 * @D3 AS "Decim 123",

  @D2 * @D3 * @D1 AS "Decim 231",

  @D3 * @D1 * @D2 AS "Decim 312",

  @D1 * @D2 AS "Decim 12",

  @D2 * @D3 AS "Decim 23"

  SELECT

  CAST((CAST((@D1 * @D2) AS Decimal(30,4)) * @D3) AS Decimal(30,4)) AS "Prec4Decim 123",

  CAST((CAST((@D2 * @D3) AS Decimal(30,4)) * @D1) AS Decimal(30,4)) AS "Prec4Decim 231",

  CAST((CAST((@D3 * @D1) AS Decimal(30,4)) * @D2) AS DEcimal(30,4)) AS "Prec4Decim 312",

  CAST((@D1 * @D2) AS Decimal(30,4)) AS "Prec4Decim 12",

  CAST((@D2 * @D3) AS Decimal(30,4)) AS "Prec4Decim 23"

  SELECT

  @F1 * @F2 * @F3 AS "Float 123",

  @F2 * @F3 * @F1 AS "Float 231",

  @F3 * @F1 * @F2 AS "Float 312",

  @F1 * @F2 AS "Float 12",

  @F2 * @F3 AS "Float 23"

  SELECT

  @N1 * @N2 * @N3 AS "Num 123",

  @N2 * @N3 * @N1 AS "Num 231",

  @N3 * @N1 * @N2 AS "Num 312",

  @N1 * @N2 AS "Float 12",

  @N2 * @N3 AS "Float 23"

  SELECT

  (@M1 * @M2 * @M3) * @R1 As "Money X Rate",

  CAST((CAST((@D1 * @D2) AS Decimal(30,4)) * @D3) AS Decimal(30,4)) * @R1 AS "Decimal X Rate",

  (@F1 * @F2 * @F3) * @R1 AS "Float X Rate"