smallmoney datatype problem - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

smallmoney datatype problem

Author

Topic

khxnheng
Starting Member

2 Posts

Posted - 2012-07-10 : 04:10:34

I have a table like below:

fundcode avgprice(float)
01 0.57055
02 0.54505

First, when I do select statement like below:
SELECT FUNDCODE, CAST(AVGPRICE AS SMALLMONEY) FROM TABLE;
Result is like this:

fundcode avgprice
01 0.5705
02 0.5451

Why 02 why do the round up but not for 01?

Second, I do a select statement with decimal like below:
SELECT FUNDCODE, CAST(AVGPRICE AS DECIMAL(8,4)) FROM TABLE;
Result is like this:

fundcode avgprice
01 0.5705
02 0.5451

It gave the same result, fundcode 01 will not round up to value 0.5706. May I know why?

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-07-10 : 07:36:04

This may be because FLOAT is an approximate representation. What you are seeing as 0.57055 may in fact be stored as 0.570549999 and 0.54505 may be stored as 0.545059, for example Try this to see the data with more accuracy:

SELECT FUNDCODE, CAST(AVGPRICE AS DECIMAL(18,10)) FROM TABLE;

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-07-10 : 12:43:30

books online clearly suggests that money and smallmoney have accuracy only upto ten thousanth of monetary unit ie scale value of 4

http://msdn.microsoft.com/en-us/library/ms179882(v=sql.100).aspx

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

khxnheng
Starting Member

2 Posts

Posted - 2012-07-12 : 05:43:11

Thanks for all the feedback.

sunitabeck, I need to have accuracy up to 4 decimal points only and thus I can't use DECIMAL(18,10).

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-07-12 : 06:54:01

quote:
Originally posted by khxnheng

Thanks for all the feedback.

sunitabeck, I need to have accuracy up to 4 decimal points only and thus I can't use DECIMAL(18,10).

I was only trying to show you why in your example one average price appears to get truncated while the other is rounded. By displaying it with a higher precision for testing purposes you would see that the behavior is consistent.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-07-12 : 09:45:08

quote:
Originally posted by khxnheng

Thanks for all the feedback.

sunitabeck, I need to have accuracy up to 4 decimal points only and thus I can't use DECIMAL(18,10).

thats what money related datatypes give for you

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2012-07-12 : 11:20:14

Are you sure about that value in row 01?

I get consistent rounding to what you were expecting:


declare @t table (ap float)
insert @t values(0.57055),(0.54505)

select cast(ap as smallmoney), CAST(ap AS DECIMAL(8,4))  from @t

OUTPUT:
--------------------- ----------------
0.5706                0.5706
0.5451                0.5451

out of curiosity try my code in your environment to see if you get the same result I got...

Be One with the Optimizer
TG

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources