Dynamic decimal precision

  • stelianx

    Mr or Mrs. 500

    Points: 544

    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: 8031

    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 @input decimal (16, 6)

    , @precision int

    , @output varchar (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

    Mr or Mrs. 500

    Points: 544

    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

    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

    Mr or Mrs. 500

    Points: 544

    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

    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

    Mr or Mrs. 500

    Points: 544

    Thanks!

    This is what I needed.

  • deepikachaudhary31

    SSC Journeyman

    Points: 85

    Thanks this is exactly what i wanted:)

  • divyaeaga

    SSC Journeyman

    Points: 91

    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

    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 10 (of 10 total)

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