asked on 12/17/2008

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!!!

Microsoft SQL Server 2008 Microsoft SQL Server 2005

Last Comment

vijay1507

12/19/2008

Sharath S

12/17/2008

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

12/17/2008

ASKER

Hi Sharat ,

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

Guy Hengel [angelIII / a3]

12/17/2008

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

12/17/2008

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

12/18/2008

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

ASKER CERTIFIED SOLUTION

Mark Wills

12/18/2008

THIS SOLUTION IS ONLY AVAILABLE TO MEMBERS.

View this solution by signing up for a free trial.

Members can start a 7-Day free trial and enjoy unlimited access to the platform.

See Pricing Options

Start Free Trial

vijay1507

12/19/2008

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