Numeric datatype conversions - Implicit and explicit

  • yogesh.l.patil 36576

    SSC Enthusiast

    Points: 105

    Hi 
    I have a situation.
    I have created temp table and inserted values in it.
    When I use them in calculation, result changes as below.
    following is the code:
    -------------------------------------
    create table #tmp(
      COL1   NUMERIC(18,4),
      COL2   NUMERIC(12,4),
      COL3   NUMERIC(12,4),
      COL4         NUMERIC(17,6)
    );
    insert into #tmp select 0.3209,1.0200,10.000,33.158000
    select (COL1 * 1 * COL2 * (1+ COL3/100)+ 0 / COL4) from #tmp
    -->0.360050
    select (0.3209 * 1.02000 * (1+ 10.0000/100) + 0/ 33.158000)
    --> 0.3600498000000000 -- expected

    -------------------------------------
    I want to know the reason why SQL is returning 0.360050 when actual calculation is returning 0.3600498000000000.
    Going ahead I have a rounding logic to 4 digits. I want this value to round --> 0.3600498000000000 and not 0.360050
    I am bit clueless. My suspect is on implicit SQL conversion. But how it is happening I am not sure.

    Kindly answer.

  • Eirikur Eiriksson

    SSC Guru

    Points: 182367

    yogesh.l.patil 36576 - Tuesday, April 3, 2018 9:38 PM

    Hi 
    I have a situation.
    I have created temp table and inserted values in it.
    When I use them in calculation, result changes as below.
    following is the code:
    -------------------------------------
    create table #tmp(
      COL1   NUMERIC(18,4),
      COL2   NUMERIC(12,4),
      COL3   NUMERIC(12,4),
      COL4         NUMERIC(17,6)
    );
    insert into #tmp select 0.3209,1.0200,10.000,33.158000
    select (COL1 * 1 * COL2 * (1+ COL3/100)+ 0 / COL4) from #tmp
    -->0.360050
    select (0.3209 * 1.02000 * (1+ 10.0000/100) + 0/ 33.158000)
    --> 0.3600498000000000 -- expected

    -------------------------------------
    I want to know the reason why SQL is returning 0.360050 when actual calculation is returning 0.3600498000000000.
    Going ahead I have a rounding logic to 4 digits. I want this value to round --> 0.3600498000000000 and not 0.360050
    I am bit clueless. My suspect is on implicit SQL conversion. But how it is happening I am not sure.

    Kindly answer.

    The precision of the expected output (17) far exceeds the table definition precision of 4 and 6 respectfully, SQL Server will not extend or increase the precision, it will round the output.
    😎

  • yogesh.l.patil 36576

    SSC Enthusiast

    Points: 105

    Thanks for reply.
    SQL gives chance to programmer for rounding explicitly.
    Can you please provide some details. Reason for SQL's behavior.
    Why does SQL not extend its precision ?
    Is it done implicitly by SQL?

    I am using SQL 2008. I am expecting value: 0.3600498000000000
    Later on I will round it to 4 digits. But As SQL is doing implicitly my further result is changing.
    Thank you in advance.

  • Eirikur Eiriksson

    SSC Guru

    Points: 182367

    yogesh.l.patil 36576 - Tuesday, April 3, 2018 11:26 PM

    Thanks for reply.
    SQL gives chance to programmer for rounding explicitly.
    Can you please provide some details. Reason for SQL's behavior.
    Why does SQL not extend its precision ?
    Is it done implicitly by SQL?

    I am using SQL 2008. I am expecting value: 0.3600498000000000
    Later on I will round it to 4 digits. But As SQL is doing implicitly my further result is changing.
    Thank you in advance.

    Have a look at this article, Precision, scale, and Length (Transact-SQL)
    😎

Viewing 4 posts - 1 through 4 (of 4 total)

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