asked on

convert float column to numeric

Hi ,

I have a float coloun with a id and values are like "2.14966375804504E+17" and when I try to convert them to Numeric using

ALTER TABLE [ttoptest].[dbo].[HDS_20080204]
ALTER COLUMN VISID numeric (25) NOT NULL

I get an error saying

Msg 8115, Level 16, State 6, Line 1
Arithmetic overflow error converting float to data type numeric.
The statement has been terminated.

can some one please help!!!

Sharath S

try to convert the folat value to decimal.

ALTER TABLE  [ttoptest].[dbo].[HDS_20080204]
        ALTER COLUMN VISID decimal(18,6) NOT NULL

Open in new window

vijay1507

ASKER

Hi Sharat ,

This is not working ,I am still getting the same error !!

Guy Hengel [angelIII / a3]

18,6 means 12 digits before the comma, and 6 after the comma, so for a value of 2.xxxE17 this is not enough

can you try bigint?
anyhow, what IS the biggest value?

select max(VISID) from yourtable

vijay1507

ASKER

HI I have also tried bigint in vien
actually this is a coloum that has been transfered from Oracle
and this is a std 17 digit number

Sharath S

agree with angel. did you run the query
select max(VISID) from yourtable

ASKER CERTIFIED SOLUTION

Mark Wills

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

vijay1507

ASKER

HI Guys I agree with all of your solutions ,I found that there is a 37 digit number in there :) so actually what I did is that I deleted the whole data and pulled the whole data from Oracle as Number 38 ( which is max I can go )

Thanks for all your support!!!
and happy Christmas