CODEWITHSUNDEEP
sql-serverdatabase-designtypes
Charles Bernardes
Charles Bernardes

Reputation: 313

What data type should be used for currency type of calculation

In my table I would like to define an amount field. This field is usually a number with only 2 decimal places

e.g. Amount can be up to 999,999,999.99 or 999,999,999

This field is multiplied by a rate.

The rate field can be be 1.11 or 0.1234 (2 decimal or 4 decimal places)

Then another field will hold the total amount that rounds the result up to 2 decimal places. If the total amount is 2.516, save result as 2.51. If total amount is 2.556, save result as 2.56

What data types should I use for the following fields? 1. Amount 2. Rate 3. Total Amount

I have seen examples defined as FLOAT, decimal, money.

Upvotes: 1

Views: 1434

Answers (2)

Aaron Bertrand
Aaron Bertrand

Reputation: 280262

I would always choose DECIMAL when given those options, especially when dealing with currency. Reasons:

  • DECIMAL can be more flexible than MONEY; the latter is fixed to 4 decimal places; usually you want less, but sometimes more (penny trading, converting euros to yen, etc).

  • FLOAT has a much wider range of approximation, which can lead to funny results - I don't get that result for that specific calculation on current versions, BTW, but there are other examples I'm sure, probably in this thread.

  • MONEY can lose important precision. Try this:

    SELECT [money]   = $0.46 / $345.70, 
       [decimal] =  0.46 /  345.70;

    Also see this thread (look beyond the accepted answer).

  • More information in the following posts:

Upvotes: 7

TheITGuy
TheITGuy

Reputation: 722

Most systems I have worked with, both existing and new use Decimal with 4 decimal places.

Upvotes: 0

Related Questions