SQL Server Performance

Arithmetic overflow error converting float to data type numeric.

Discussion in 'SQL Server 2008 General Developer Questions' started by apat, Mar 5, 2010.

  1. apat New Member

    I imported some data from text file to sql server 2008. Import Export wizard created a table with varchar(255) for some columns which had float data. Then I altered those columns and converted them to float.
    One of these columns (Column1) has data like (-8.92,-10.34,-19.97,-7.62,-17.82,1.2,1.86,0.37) There are 120K records in total. This column has records ranging from -986.145 to 975.81. I have to create a new column from this column by applying this...
    select POWER (1.8, Column1) * 10000.0 as NewValue from #temp
    When I do this I get following error: Msg 8115, Level 16, State 6, Line 1 Arithmetic overflow error converting float to data type numeric.
    Can someone please help me with this? Thanks in advance...
  2. Adriaan New Member

    Perhaps you have to cast the other factor in the multiplication to float:select
    POWER (1.8, @Column1) * CAST(10000.0 as float)
    From what I hear, float isn't the most predictable data type - why use it in the first place?
  3. apat New Member

    Thanks for your reply Adriaan...
    Perhaps I have to do,
    select POWER (1.8, @Column1) * CAST(10000.0 as float)
    if I do this, I get an error:Msg 3623, Level 16, State 1, Line 1
    An invalid floating point operation occurred.
  4. moh_hassan20 New Member

    that error is due to the accuracy of power function.
    for example ,run the following code:
    example 1: with overflow
    declare @i float
    set @i =100
    while @i <975
    begin
    print str (@i )
    print power (1.8 ,@i)
    set @i =@i+1
    end

    --The power (1.8, 145) raise error "Arithmetic overflow error converting float to data type numeric."
    example 2: avoid overflow

    declare @i decimal

    set @i =100
    while @i <975
    begin
    print str (@i )
    print power (cast (1.8 as float) ,@i)
    set @i =@i+1
    end
    -- no errors
    The solution
    select POWER ( cast (1.8 as float), @Column1) *10000.0
    The first parameter in POWER function should be float (else it will be treated as REAL)
    so
    select POWER ( cast (1.8 as float), 975.81) *10000.0 give the value
    1.25159322862048E+253

Share This Page