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...
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?
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.
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