converting float to decimal

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

  • 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))

  • thanks for the reply...

    unfortunately it still errors out with the arithmetic overflow error.

    this is really unusual.

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

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

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

  • 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)

  • 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

  • You said you had a float to decimal converion that errors out. Maybe you could start by posting an example of that?

  • 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

  • You need to use the code like this:

    SELECT

    CAST(MyFloatColumn AS DECIMAL(11,2)) AS [Decimal],

    MyFloatColumn AS [Float]

    FROM MyTable

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

    _________________________

  • 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 🙂

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

  • 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