Dynamic decimal precision

  • 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?

  • 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)

  • 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.

  • 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

  • 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.

  • 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))

  • Thanks!

    This is what I needed.

  • Thanks this is exactly what i wanted:)

  • 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!!

  • 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))

Viewing 10 posts - 1 through 9 (of 9 total)

You must be logged in to reply to this topic. Login to reply