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

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

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

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.

• Eirikur Eiriksson

SSC Guru

Points: 182367

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

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.