Arithmetic operation failure. Is this a bug, or am I doing something wrong?

Hot Topic

Cannot create a connection to data source 'mydatasourcename'.
Joining two fields in a query
Copied Package ConnectionManager has photographic memory?
Report Models using Islookup and expandinline
Problematic Stored Procedures using Views with Triggers
Overriding repeating totals from non-referenced slicer dimension
Installing SQL Server 2005 Express edition
Date Concatenation
Eventhandlers don't work if a package is started from a checkpoint - BIG NASTY BUG (I think)
firehose

SQL Server

A rewrite of the sp_help_revlogin procedure (use at own risk)
“AcquireConnection method call to the OLEDB Connection Manager for SQL Server failed” Error
MSDE Connection problems
SQL Server 2005 Management Studio can't be found
How to get the URL to access a report?
Accessing Mobile SQL Databases in VB2005 - Windows Apps
Putting reusable code into SSIS components....
Problems with remote SQL 2005 server and excel as an input source
Creating a Stored Proc from a particular template?
Failed to generate a user instance of SQL Server due to failure in retrieving the user's local application data path

Arithmetic operation failure. Is this a bug, or am I doing something wrong?

I used the following code in a function. The @infloat is input parameter

For every other number than 16.31, 17.31, 18.31, 19.31 and 20.31 (at least those numbers I checked) the code runs ok. For those numbers the result is wrong.

What I want to do is return a ten digits number left filled with zeros, with the last two digits consitered as decimals.

What I do is multiply the given float with 100.00, and take the resulting integer

But when @infloat is multiplied with 100.00 and @infloat in (16.31, 17.31, 18.31, 19.31 and 20.31)

the result is (1630,1730,1830,1930,2030) instead of (1631,1731,1831,1931,2031)..

Can anyone try this

declare @ingood int,

@stuffme varchar(10),

@infloat float, @infl float

set @infloat=18.31

set @infloat=isnull(@infloat,0)

set @stuffme='0000000000'

set @infl=@infloat*(100.00)

select @infl as infl

set @ingood=cast(@infl as int)

select @ingood as ingood

set @stuffme=stuff(@stuffme,11-len(@ingood),len(@ingood),@ingood)

select @stuffme




Answer this question

Arithmetic operation failure. Is this a bug, or am I doing something wrong?

  • mhammingh

    Do you need high precision Floating point data is approximate; not all values in the data type range can be precisely represented.

    See here :
    select convert(float, 18.31)
    Result : 18.309999999999999





  • DBoldt

    Hi

    I have tried MONEY and NUMERIC datatypes for @infloat and @infl from your example. They both work just fine.

  • twistedinferno

    Replace

    set @ingood=cast(@infl as int)

    WITH

    set @ingood=cast(@infl as NUMeERIC (10,2))
  • Arithmetic operation failure. Is this a bug, or am I doing something wrong?
Answer this question