Click here to Skip to main content
15,867,895 members
Articles / Database Development / SQL Server / SQL Server 2012
Tip/Trick

Money Precision Issues

Rate me:
Please Sign up or sign in to vote.
5.00/5 (5 votes)
15 Feb 2015CPOL 10.2K   3   1
This tip shows some primary issue which puts a serious limitation on SQL Server MONEY data type.

Introduction

Many developers like MONEY data type as it tends to be faster during computations and byte cheaper (arguably). Still in the next few examples, I am going to show an issue that can lead to possible loss of precision.

Examples

Let us try the following code:

The actual result is 0,054297 and by all mathematical rules should be rounded to 0,0543. Still, the query result is 0,0542.

Let's look at another example:

The actual result is 21,2939664 and the query returns 21,294.

This kind of behaviour is called banker's rounding. However, if you use SQL Server DB combined with .NET application, you may encounter precision loss as .NET doesn't use banker's rounding for decimal data type by default.

One of the possible solutions is to cast money to more precise data type before such operations. The following example works properly:

History

  • 16th February, 2015: Initial version

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)


Written By
Team Leader
Ukraine Ukraine
Team leader with 8 years of experience in the industry. Applying interest to a various range of topics such as .NET, Go, Typescript and software architecture.

Comments and Discussions

 
SuggestionThat note about DataType: Money. Pin
José G. Ramírez (Koaltares)19-Feb-15 6:52
José G. Ramírez (Koaltares)19-Feb-15 6:52 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Praise Praise    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.