CAST money AS FLOAT then INT returns unexpected result for 0.57 when FLOAT is multiplied by 100 by Puzzled Robin

Closed

as By Design Help for as By Design

Type:	Bug
ID:	777875
Opened:	1/30/2013 4:48:54 AM
Access Restriction:	Public

Workaround(s)

view

User(s) can reproduce this bug

PRINT CAST(CAST(CAST(0.57 as money) AS FLOAT)*100 AS INT)

I would expect the result to be 0.57 but the result returned is 56. Also returns unexpected values for 0.29 and 0.58.

Details (expand)

English

SQL Server 2008 R2 SP1

SQL Engine

Windows Server 2008 Standard

US English

PRINT CAST(CAST(CAST(0.57 as money) AS FLOAT)*100 AS INT)

X64

0 attachments

Posted by Microsoft on 2/5/2013 at 12:26 PM

Hello,
The behavior you are seeing is by design. Float datatype uses approximate representation and in addition to that we truncate float values when converting to integer data types. See the link below:

http://msdn.microsoft.com/en-us/library/ms173773.aspx

Use decimal data type if you want precision & even with those types you need to be aware of the precision/scale changes based on the arithmetic operation.

--
Umachandar, SQL Programmability Team

Posted by Richard Douglas on 2/1/2013 at 5:25 AM

There's a pretty big disclaimer in Books Online already - "Approximate-number data types for use with floating point numeric data. Floating point data is approximate; therefore, not all values in the data type range can be represented exactly. "

If you change your code to the following then it will be correct:
PRINT CAST(CAST(CAST(0.57 as money) AS DECIMAL(9,2))*100 AS INT)

Hope this helps,
Rich

Microsoft