Simple conversion to 2 decimal places

• Isabelle2378

  SSCertifiable

  Points: 6174

  More actions

  March 2, 2011 at 4:51 pm

  #388954

  Hi,

  I have a pretty simple conversion that I can't quite get to work properly. I have two columns

  OANTAM numeric(15,2) VALUE = 463.60

  OAOTDP numeric(13,9) VALUE = 5.0000000000

  and I am just trying to do a percentage calculation with the results with two decimal places.

  When I do the query, my result is 23.180000000000000. I am trying to strip all the zeros off and just get 23.18. So I have been looking at different things to try and keep getting results of 23.1800.

  I have tried using CAST and CONVERT:

  SELECT OANTAM * cast(OAOTDP/100 as decimal(10,2))

  FROM MVXJDTA.OOHEAD WITH (NOLOCK)

  WHERE OAORNO = '1001357174'

  result: 23.1800

  I have tried using CASE:

  SELECT OANTAM *

  CASE ISNUMERIC(OAOTDP/100)

  WHEN 1 THEN CONVERT(NUMERIC(18,2),OAOTDP/100)

  ELSE 0.00

  END

  FROM MVXJDTA.OOHEAD WITH (NOLOCK)

  WHERE OAORNO = '1001357174'

  result: 23.1800

  What am I missing? :unsure:

  Isabelle

  Thanks!
  Bea Isabelle

• Isabelle2378

  SSCertifiable

  Points: 6174

  More actions

  March 2, 2011 at 4:58 pm

  #1293806

  Got it....found another example in the past posts on the forum....

  SELECT CAST(ROUND(OANTAM * (OAOTDP/100),2)AS DECIMAL(10,2))

  FROM MVXJDTA.OOHEAD WITH (NOLOCK)

  WHERE OAORNO = '1001357174'

  result: 23.18

  Is it time to go home yet???? LOL :hehe:

  Thanks,

  Isabelle

  Thanks!
  Bea Isabelle