Numeric datatype conversions - Implicit and explicit

• yogesh.l.patil 36576

  SSC Veteran

  Points: 275

  More actions

  April 3, 2018 at 9:38 pm

  #149469

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

  More actions

  April 3, 2018 at 11:13 pm

  #1985397

  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 Veteran

  Points: 275

  More actions

  April 3, 2018 at 11:26 pm

  #1985399

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

  More actions

  April 4, 2018 at 1:02 am

  #1985402

  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)