Convert source real data type to target decimal(18,5) data type .

Question

Convert source real data type to target decimal(18,5) data type .

DBA.k

Ten Centuries

Points: 1113
More actions
April 2, 2019 at 11:07 am

#2493993

I have source table Parcel_Area column with Real datatype. But target table i have AREA column with decimal(18,5). data conversion time source table i have value '131281' this value while loading into target table it was inserted with '131281.01563'. How can i solve that even i ahve applied convert(decimal(18,5),Parcel_Area) but still have same problem. But the same i have created #temp tables and data loaded properly.
Source
PID PARCEL_AREA
0102724120004131281
Target:
CPID AREA
0102724120004131281.01563

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

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

Thom A SSC Guru Points: 99130 More actions · Answer 1

I can't replicate your problem db<>fiddle. Could you post us some sample formatted DDL and DML so help us replicate the problem, and explain what the results you're after are?

Note, however, that a real is only accurate to 7 digits, so why are you using a decimal(18,5)? Do you have some values that are less than 100 and have 5 decimal places, and others that nearing 10 Trillion?

This reply was modified 5 years, 8 months ago by Thom A.

Thom~

Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk

Sergiy SSC Guru Points: 110208 More actions · Answer 2

Can the actual PARCEL_AREA value possibly have any decimal digits?

What's the idea behind choosing the data type decimal(18,5) for storing its value?

Shouldn't it be some CHAR(6), or at least INT data type?

_____________
Code for TallyGenerator

rVadim Hall of Fame Points: 3969 More actions · Answer 3

I was able to reproduce your problem when I added .01 to the original value. Maybe in the table you have 131281.01 but don't see it in the output?

DECLARE @R REAL          = 131281.01
DECLARE @F FLOAT(53)     = 131281.01
DECLARE @D DECIMAL(18,5) = CONVERT(DECIMAL(18,5), @R)

SELECT R = @R, D = @D, F = @F

Results:

R             D            F
------------- ------------ ---------
131281        131281.01563 131281.01

Note how R doesn't show digits after the decimal point. It seem to be somewhat known issue and appear to be due to the size of REAL - it's actually FLOAT(24). If I increase the size (see F) or reduce number of digits in the R, it works.

Also, check this. Looks like Aaron has solution:

https://stackoverflow.com/questions/11119343/convert-float-to-decimal-sql-server

--Vadim R.