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

  • DBA.k

    Right there with Babe

    Points: 797

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

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

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

    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

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

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