Dynamic decimal precision

• stelianx

  SSChasing Mays

  Points: 604

  More actions

  May 6, 2009 at 6:41 am

  #99685

  I need the precision of a decimal value to be dynamically controlled. Example:

  DECLARE @input as decimal(16,6)

  SET @input = 1.234567

  DECLARE @precision INT

  SET @precision = 2

  -- SELECT CAST(@input AS DECIMAL(16, @precision)) <-- This does not work
  -- SELECT CAST(@input AS DECIMAL(16, 2)) <-- This works This way, If @precision = 2

  1.23 should be returned

  If @precision = 3

  1.234 should be returned

  Is this possible?

• Kishore.P

  SSCrazy Eights

  Points: 8147

  More actions

  May 6, 2009 at 7:03 am

  #989488

  stelianx (5/6/2009)

  ---

  I need the precision of a decimal value to be dynamically controlled. Example:

  DECLARE @input as decimal(16,6)

  SET @input = 1.234567

  DECLARE @precision INT

  SET @precision = 2

  -- SELECT CAST(@input AS DECIMAL(16, @precision)) <-- This does not work

  -- SELECT CAST(@input AS DECIMAL(16, 2)) <-- This works

  This way,

  If @precision = 2

  1.23 should be returned

  If @precision = 3

  1.234 should be returned

  Is this possible?

  try the following way:

  declare@inputdecimal (16, 6)

  ,@precisionint

  ,@outputvarchar (1000)

  select@input= 1.234567

  ,@precision= 4

  select@output= 'select cast (' + convert (varchar (100), @input) + ' as decimal (16, ' + convert (varchar (100), @precision) + ')) '

  print(@output)

  exec(@output)

• stelianx

  SSChasing Mays

  Points: 604

  More actions

  May 7, 2009 at 12:21 am

  #990143

  That worked, thanks.

  Now I am wondering how this can be used in a view

  I guess it is not possible. I was thinking about creating an udf to format decimal, but it's semed impossible to me.

• arun.sas

  SSChampion

  Points: 11831

  More actions

  May 7, 2009 at 2:39 am

  #990183

  Hi Kishore,

  When the value like 1.23999

  Then it’s round the value

  Select @input = 1.23999, @precision = 4

  Result to 1.2400

  For that better we use the direct round function?

  Like select round(@input,@precision)

  ARUN SAS

• stelianx

  SSChasing Mays

  Points: 604

  More actions

  May 7, 2009 at 8:14 am

  #990364

  Well, it is "almost" good

  PRINT round(1.23456, 2)

  displays 1.230000, while I need 1.23

  I know it is not a good behaviour to format from SQL, but I really have to.

• rgillings

  SSCrazy

  Points: 2114

  More actions

  May 8, 2009 at 8:48 am

  #991162

  If the value is for display purposes, and not required to be a decimal data type, you could format it as a string like below:

  DECLARE @Input DECIMAL(16,6)

  DECLARE @precision INT

  SELECT @input = 1.23999, @precision = 4

  SELECT CONVERT(VARCHAR, SUBSTRING(CONVERT(VARCHAR,round(@input,@precision)), 0, CHARINDEX('.', CONVERT(VARCHAR,@input),0) + @precision + 1))

• stelianx

  SSChasing Mays

  Points: 604

  More actions

  May 8, 2009 at 8:58 am

  #991175

  Thanks!

  This is what I needed.

• deepikachaudhary31

  SSC Journeyman

  Points: 85

  More actions

  October 7, 2011 at 5:34 am

  #1391641

  Thanks this is exactly what i wanted:)

• divyaeaga

  SSC Journeyman

  Points: 91

  More actions

  January 1, 2015 at 11:09 pm

  #1767341

  HI..

  How to do the dynamic precision from a table

  for ex: I have a table Currency with Rate (float) and Precision.

  Currency Rate precision

  AUD 12.12 5

  AED 123.123123 7

  I want to get these values as

  AUD 12.12000

  AED 123.1231230

  Please help me. It is urgent!!

• brijeshbajpai

  SSC Rookie

  Points: 37

  More actions

  October 12, 2017 at 1:07 am

  #1963318

  rgillings - Friday, May 8, 2009 8:48 AM

  declare @precision int =2
  declare @ddd decimal(16,6)=10.9555
  select convert(float,round(@ddd,@precision))