April 3, 2018 at 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.
April 3, 2018 at 11:13 pm
yogesh.l.patil 36576 - Tuesday, April 3, 2018 9:38 PMHi
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.
😎
April 3, 2018 at 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.
April 4, 2018 at 1:02 am
yogesh.l.patil 36576 - Tuesday, April 3, 2018 11:26 PMThanks 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 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply