January 31, 2008 at 9:59 am
every time i try this conversion by simply changing the datatype value from float to
decimal it errors out with an arithmetic overflow error.
is there any easy way to do this? maybe some new feature/function in sql 2005 that
i don't know about?
January 31, 2008 at 3:04 pm
You need to set your decimal percision correctly otherwise it will overflow as a general rule of thumb, you can count the digits to determine what you percision should be and your scale is the number of decimal places.
e.g.
declare @var float
set @var = 132456789.12
select cast(@var as decimal(11,2))
February 1, 2008 at 1:13 pm
thanks for the reply...
unfortunately it still errors out with the arithmetic overflow error.
this is really unusual.
February 1, 2008 at 1:55 pm
simsql (2/1/2008)
thanks for the reply...unfortunately it still errors out with the arithmetic overflow error.
this is really frustrating.
Don't be shy about posting the code that you want help with.
February 1, 2008 at 2:18 pm
here's the situation.
i have a few million records that currently exist as a float which i need converted to
decimal (25, 10) datatype, but whenever converted from Management Studio or
Enterprise Manager it errors out.
the reason i mention the 2 is because i'm left having to migrate the data from
one sql 2000 table to a sql 2005 table. so naturally i've tried the conversions
from both ends.
i move the data over as a float, and try to convert to decimal. i have no idea how to
do this while importing.
February 1, 2008 at 2:35 pm
quick recap
declare @var float
set @var = 132456789.12
select cast(@var as decimal(11,2))
i'm setting it up like this though i doubt it will work.
declare @var float
set @var = (table.column)
select cast(@var as decimal(11,2))
ideas?
February 1, 2008 at 2:58 pm
This works Ok for me in SQL Server 2000 and 2005:
declare @var float
set @var = 132456789.12552346
select [Decimal] =cast(@var as decimal(11,2)), [Float] =@var
Results:
Decimal Float
------------- -----------------------------------------------------
132456789.13 132456789.12552346
(1 row(s) affected)
February 1, 2008 at 3:06 pm
according to this example though...
it looks as though it specifies only one record.
set @var = 132456789.12552346
thats fine cause i can concat the entire script
against every records (few million rows), then run
the bulk script, but is there a way to set the @var
to the entire column, and not per row?
e.g.
set @var = mytabe.column_name
February 1, 2008 at 3:11 pm
You said you had a float to decimal converion that errors out. Maybe you could start by posting an example of that?
February 1, 2008 at 3:22 pm
first... thanks for all the good feedback. it's really appreciated.
the conversion that i did was using the gui. where you right click the table (FinMain), and
select 'design' then you get all the columns, and their datatypes.
i have a column: ValData float
so i simply hit the drop down and selected Decimal (25, 10) for ValData
then i clicked save.
get the error: "...arithmetic overflow" it's been suggested that cast and convert
is all thats needed, but i'm not convinced.
thoughts?
declare @var float
set @var = (select ValData from FinMain)
select [Decimal] = cast(@var as decimal(25,10)), [Float] =@var
February 2, 2008 at 5:52 pm
You need to use the code like this:
SELECT
CAST(MyFloatColumn AS DECIMAL(11,2)) AS [Decimal],
MyFloatColumn AS [Float]
FROM MyTable
February 2, 2008 at 7:55 pm
this seems to be a problem from time to time....
i've done conversions which shouldn't be too difficult; yet for some reason will
still give me trouble. nice to know threads like this are still active.
any information about this sort of this is helpful.
_________________________
February 2, 2008 at 8:42 pm
adam... thanks for the samples
so far so good.... running past a couple million rows, and no issues...
i'll post an update when it's finished.
again... thanks 🙂
February 2, 2008 at 8:47 pm
ahh well... it was a pretty good run.
still get this error though:
Msg 8115, Level 16, State 6, Line 1
Arithmetic overflow error converting float to data type numeric.
i think cause there are a few hundred float values of something like this:
5.87348091654376E+15
8.56784091067654E+15
7.58674490270345E+15
3.56734927376573E+15
:unsure: must be a way to get this done... still scratching my head
on this one.
February 2, 2008 at 8:59 pm
so i simply hit the drop down and selected Decimal (25, 10) for ValData
----------
make sure 25 is large enough. you can try Decimal (38, 10)
Viewing 15 posts - 1 through 15 (of 31 total)
You must be logged in to reply to this topic. Login to reply