1

When looking for the difference between SQL data types money and decimal I came across several posts that suggested that decimal(19,4) was best used to represent currency in SQL Server

Why are four places to the right of the decimal needed? I would think that only two places are required as money is represented as 

$23.47
$5.00
$4200.50

I don't recall ever seeing money represented as 

$23.4700
$5.0000
$4200.5000
webworm
  • 10,587
  • 33
  • 120
  • 217
  • 4
    Read up on Banker's rounding you'll learn why. Here's [one link](https://www.eecis.udel.edu/~breech/contest.inet.fall.07/problems/bankers-rounding.html) which shows why it could be problematic to just store 2 values. – xQbert Dec 07 '15 at 21:56
  • 4 decimal places is standard in financial applications that calculate interest. The results are typically then rounded to 2 decimal places for practical purposes. – Sean Lange Dec 07 '15 at 21:56
  • Thanks for the link to `Bankers Rounding`. If I am writing an application that just deals with two decimal places ie. `$25.55`, is there any reason to use four decimal places? The application makes no calculations other than Price x Quantity = Subtotal and the additional Sales Tax. – webworm Dec 07 '15 at 22:02
  • Have you checked what kind of compliance requirements the application may have? There could be some that would want the extra digits. Possible examples would be PCI and SOX. – JB King Dec 07 '15 at 22:04

1 Answers1

2

The first and most important rule: use a decimal data type, never ever binary floating-point types.

When exactly rounding should be performed can be mandated by regulations, such as the conversion between the Euro and national currencies it replaced.

If there are no such rules, I'd do all calculations with high precision, and round only for presentation, i.e. not use rounded values for further calculations. This should yield the best overall precision.

solar411
  • 832
  • 1
  • 12
  • 35
  • I agree with your phrase `never ever binary floating-point types`, but `money` type in SQL Server is not a floating-point type. It is exact. [The money and smallmoney data types are accurate to a ten-thousandth of the monetary units that they represent.](https://msdn.microsoft.com/en-AU/library/ms179882.aspx) – Vladimir Baranov Feb 22 '16 at 12:17