In a currency exchange rate what is the maximum number of decimal places used?

Question

I need to store currency exchange rate data in the SQL 2008 database. I would like to keep rate as a decimal data type in the database.

One thing I am not sure about, is how many decimal places to give (e.g. decimal(18, ?)).

Since, I will be using this service as a source: http://openexchangerates.org/latest.json. There, it seems that the highest number of decimal places used is '6'.

Is that the right number, or there possibly can be rates with more than 6 decimal places?

Decadence

Why not use the Money data type? – Rosauraroscius

Most of the time it's not even that much. Usually you're gonna end up using 2 or 3 decimal places. I'd say stay with 6 just in case – Gog

How many decimal places does the source data have? – Salient

I didn't use Money datatype because of this: #583297 I think I'll just use 6 places since in source data I didn't find any rate with more than 6 spaces. – Decadence

Answer 1

I once worked on an international financial C# based package, which had to support numerous currencies, and we supported 6 decimal places. We had a couple of very experienced designers who told us that would be sufficient.

Florencia

Not sufficient for e.g. BitCoin anymore – Asset

6 decimal places are not enough i.e. for Vietnam Dong: 1USD = 21,831.08VND, 1VND = 0.000045806254203USD – Lightner

Answer 2

Nowadays, to support the unlikely exchange rate of XBT/VND, you'll need 10 to 14 decimal places

As of 2017-11-20,

1 XBT = 185,416,429.63 VND
1 VND = 0.00000000539327 XBT

Ragman

Answer 3

There is not an easy answer on that. The question is what you want to store:

• is it the final currency amount of an invoice?
• or is it maybe the line amount of an document line (e.g. the line amount of an invoice line)?

Final currency amount

I only know currencies with 2 decimal places but according to this doc from Oracle the ISO standard says 3 decimal places are allowed in a currency. (I couldn't find the ISO document Oracle is mentioning here).

Interesting: SQL Server has a data type money and smallmoney which supports 4 decimal places. Don't know how they came up with that, maybe because of rounding issues (see below).

Line amount of an document line

You might want to make sure that you don't run into rounding issues and therefore use more than 3 decimal places where you have a 'sub-amount' of the total amount of a document (e.g. invoice).

Example:

Line	Qty	Price	Discount	Line Amount	Line Amount rounded 2 decimal places
Item A	5	79.99	3%	387.9515	387.95
Item B	2	56.12	3%	108.8728	108.87
Item C	1	12.19	3%	11.8243	11.82
Total				508.65	508.64

We end up having 1 cent rounding difference when we round the line amount to two decimal places. You can play this game with other numbers where you run into differences with more than three decimal places.

The most systems I worked with use different approaches:

• use 5 or more decimal places in the line amount. With 5 decimal places, most if these rounding issues should be soved. I saw ERP systems with 10 decimal places, but I saw this more like an bad application design.
• 'enforce' rounding in the line amount to the currency amount max. decimal places. This makes sence when you want to print the 'Line amount' in the invoice and you don't want to make printed invoices where the rounded line amount does not match up with the total
• make it configurable for the application how many decimal places shall be used.
• adding a 'rouding' line to a document to discount the rounding in case the rounding issues would be to the disadventage of the customer.
• warn the application user when rounding issues occur and ask for how the system shall handle them

It is up to your application design how you want to deal with rounding issues in document lines.

Distribute

Rounding errors are inevitable. Check this: 1.783014 + 1.311594 + 1.420394 = 4.515002 (4.52), now rounding to 5 decimal places 1.78301 + 1.31159 + 1.42039 = 4.51499 (4.51) – Soupandfish

You are right on that. You can’t avoid rounding issues completely. Another option I have seen is to add an „Remain Rounding“ column to hold the last digits for the last rounding e.g. on Header Level. But even then you can’t avoid it 100%. But you will minimize rounding issues. – Distribute