# Which data type decimal, money, float...? - Microsoft SQL / MS SQL Server

For a sql server 2000 table that must hold exchange rates for currency and fractions of hours that will be multiplied by currency values, is it (in general) best to use a decimal data type or a smallmoney / money data type? The values are small, i.e. a typical exchange rate might be 1.3367 or a fraction of hours might be 1.5, 1.25 etc. The calculations will typically be done in a query or stored procedure Problem is that it's used for financial calculations and I'm not sure of the implications of money versus decimal. (I think float is completely ...

1. ## Which data type decimal, money, float...?

For a sql server 2000 table that must hold exchange rates for currency and
fractions of hours that will be multiplied by currency values, is it (in
general) best to use a decimal data type or a smallmoney / money data type?

The values are small, i.e. a typical exchange rate might be 1.3367 or a
fraction of hours might be 1.5, 1.25 etc. The calculations will typically
be done in a query or stored procedure

Problem is that it's used for financial calculations and I'm not sure of the
implications of money versus decimal.

(I think float is completely out due to rounding errors and approximation)

I guess what I'm getting at specifically is, if I use a decimal type let's
say 1.33567 in a calculation in a query or a stored procedure, what happens
if the result of the calculation is 999,999,999.9999999999? Is it an
overflow because one of the values is a decimal type of only 5 significant
digits, or is the result independant of the data types used in the
calculation?

JCardinal Guest 2. ## Re: Which data type decimal, money, float...?

Both money and decimal are exact data types, so they are preferable to float
in financial applications, as you correctly stated. Between them, it is a
matter of taste. I prefer decimal, because money includes presentation of
the data, and I like to have the data and the presentation of the data
separated.

--
Dejan Sarka, SQL Server MVP
FAQ from Neil & others at: http://www.sqlserverfaq.com
PASS - the definitive, global community
for SQL Server professionals - http://www.sqlpass.org

"JCardinal" <com> wrote in message
news:phx.gbl...
type?
the
happens

Dejan Guest 3. ## Re: Which data type decimal, money, float...?

IMO, for exchange rates, you should not use money, because these are not
a money amounts. So for that I would choose decimal.

For currency values you could choose (small)money or decimal.

As for the overflow question: if the result of a calculation is
(implicitely or explicitely) converted to a decimal or money datatype,
then it should fit in its definition. The least significant digits will
be rounded if the money/decimal data type has lower precision.

Gert-Jan

JCardinal wrote:
Gert-Jan Guest #### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•