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

• DBA.k

SSC Eights!

Points: 852

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

0102724120004 131281

Target:

CPID AREA

0102724120004 131281.01563

• Thom A

SSC Guru

Points: 98273

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 4 months, 3 weeks ago by  Thom A.

Thom~

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

• Sergiy

SSC Guru

Points: 109668

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?

• rVadim

Hall of Fame

Points: 3897

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.01DECLARE @F FLOAT(53)     = 131281.01DECLARE @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.

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

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