NUMERIC Precision Scale Issue

1

Hi,

Am using Excel to upload the data into SQL Server. A column contains some decimal values. And while use to upload in the DB, Values precision scale is taken in default with some values.

Ex: Excel column : V1 = 2.36031
My DB column : V1 with data type as NUMERIC(27,15)

While fetching the Value from Excel into tempTable am getting the same value. On doing the CAST value it changes.
CAST(V1 as NUMERIC(27,15))

V1 value used to inserted in DB as 2.360309999999980

But my actual value to be 2.360310000000000

Please help to fix this issue.

Regards,
Kalaiselvan R

2

works for me:

create table t(V1 NUMERIC(27,15))
insert into t(V1)
select CAST(2.360309999999980 as NUMERIC(27,15)) as v1

select * from t

yields

V1
2.360309999999980
3

Britton,

Your answer is not my expected result.
Value must be dynamic. From Temp table the value is V1 = 2.36031.

And i need to upload the value as 2.360310000000000. But its coming as 2.360309999999980.

4

use the round function:

insert into t(V1)
  select round(2.360309999999980,6) as v1
5

Again its a wrong result. I cant able to round off.
If my Temp table value is 56.123456789123456.
So my DB upload value also the same above, It shouldn't round off.
Finally i need the exact value in DB which comes from Temp table.

6

In that case I'm not sure what you want. You said

V1 value used to inserted in DB as 2.360309999999980

But my actual value to be 2.360310000000000

Which to me says "I have 2.360309999999980 but I want 2.360310000000000 inserted."

If that's not what you mean, I'm lost.

7

Used to inserted means - Currently its been inserted like this 2.360309999999980.
But my actual value is 2.36031. So it should be inserted as 2.360310000000000.

While casting the Real value 2.36031 to NUMERIC(27,15) its been converted as 2.360309999999980.
But required output to be 2.360310000000000 after converting. Check was value should not be round off, Since my value must be same as the Temp table input.

8

That's because Reals are floating point, which by their binary nature are imprecise. That is, 2.36031 cannot be precisely stored in a floating point number. That's why I'd use ROUND in your case.