You must Sign In to post a response.
  • Category: SQL Server

    Display decimal precision based on the number we given

    Hi There,

    DECLARE @V_Precision Int = 0
    declare @V_ResultValue decimal
    set @V_ResultValue= 123.7564

    i want the output 123.75 if i pass @V_Precision 2 not 123.76..if i pass 3 it will display 123.756

    Thanks in advance
  • #764191
    1. Multiply the value with V_Precision times of 10
    2. Use the Math.Truncate
    3. Divide the value with V_Precision times of 10
    You can try the following logic for get the result

    int V_Precision = 2;
    double V_ResultValue = 123.7564;
    for (int i = 0; i < V_Precision ; i++)
    {
    V_ResultValue *= 10;
    }

    V_ResultValue = Math.Truncate(V_ResultValue);

    for (int i = 0; i < V_Precision ; i++)
    {
    V_ResultValue /= 10;
    }

    By Nathan
    Direction is important than speed

  • #764203
    You can use

    Select round(V_@_ResultValue ,@V_Precision ).

  • #764233
    You can use Round method in sql, see below sample
    cast(round(123.7564,2) as numeric(36,2)) the result will be
    123.75
    or use Format method
    DECLARE @i FLOAT = 6.677756

    SELECT
    ROUND(@i, 2)
    , FORMAT(@i, 'N2')
    , CAST(@i AS DECIMAL(18,2))
    , SUBSTRING(PARSENAME(CAST(@i AS VARCHAR(10)), 1), PATINDEX('%.%', CAST(@i AS VARCHAR(10))) - 1, 2)
    , FLOOR((@i - FLOOR(@i)) * 100)
    ---------------------------
    Output
    6,68
    6.68
    6.68
    67
    67

    Thanks
    Koolprasd2003
    Editor, DotNetSpider MVM
    Microsoft MVP 2014 [ASP.NET/IIS]

  • #764240
    Dynamic SQL will do the trick for you.

    Please check this below SQL and see if it helps


    DECLARE @V_Precision Int = 3
    declare @V_ResultValue FLOAT
    set @V_ResultValue= 123.7564875576
    DECLARE @sql NVARCHAR(2000)
    set @sql = 'select CAST(' + cast(@V_ResultValue as varchar(20)) + ' AS DECIMAL(18, ' + cast(@V_Precision as varchar(10)) + '))'
    exec(@sql)

    Please mark this as Answer, if this helps

    Regards,
    Alwyn Duraisingh.M 
    << Database Administrator >>
    Jesus saves! The rest of us better make backups...


Sign In to post your comments