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

  • DBA.k

    Ten Centuries

    Points: 1085

    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.



    0102724120004 131281



    0102724120004 131281.01563

  • Thom A

    SSC Guru

    Points: 98623

    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 1 year, 2 months ago by  Thom A.


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

  • Sergiy

    SSC Guru

    Points: 109692

    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: 3969

    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

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


    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:

    --Vadim R.

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

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