Calculation Error - 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

Transact-SQL (2000)

Calculation Error

Author

Topic

Calen
Starting Member

2 Posts

Posted - 2005-01-14 : 14:08:21

Hi all,

Can anyone tell me why these two simple calculations produce different results?

SELECT (110.09 / 1000 * 502750) AS RAW_CALC_RESULT

SELECT (CAST(110.09 AS Money) / CAST(1000 AS Integer) * CAST(502750 AS Integer)) AS CAST_CALC_RESULT

RAW_CALC_RESULT = 55347.7475 and CAST_CALC_RESULT = 55302.5000, a difference of 45.2475.

This is based on a real problem I am having with some sales invoices. The datatypes that I cast the numbers into match the data of the invoices.

Thanks in advance to all who reply,

Calen

tuenty
Constraint Violating Yak Guru

278 Posts

Posted - 2005-01-14 : 14:33:32

I've wouldn't use the cast between calculations. If you really want to use cast then use it for the
results like

SELECT cast((110.09 / 1000 * 502750) as money) AS RAW_CALC_RESULT

if you do it with a calculator you'll get this 55347.7475

.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.
A candle loses nothing by lighting another candle

Stoad
Freaky Yak Linguist

1983 Posts

Posted - 2005-01-14 : 15:56:37

Even more interesting is simply this:

SELECT CAST(110.0000 as money)/1

---------------------
19364346248822.5524

And I can say nothing on this...

jen
Master Smack Fu Yak Hacker

4110 Posts

Posted - 2005-01-17 : 00:00:08

quote:
Originally posted by Stoad

Even more interesting is simply this:

SELECT CAST(110.0000 as money)/1

---------------------
19364346248822.5524

And I can say nothing on this...

i got 110.0000

--------------------
keeping it simple...

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2005-01-17 : 03:31:59

yeah me too... 110.0000

Go with the flow & have fun! Else fight the flow