1

What is the difference between money & numeric data type from point of practical usage. If one table has money as one of the data type (field name say BreakupAmount) & another one of numeric type (field name may or may not be same) then is it possible to post entries from these two fields into a third table where data type is numeric (field name may or may not be BreakupAmount)? Any suggestions?

Lalu
  • 168
  • 1
  • 2
  • 12

3 Answers3

2

Never use MONEY; 1) It is proprietary, so porting it is a pain. It is one of the many "Sybase Code Museum" features from decades ago. Remember the early versions of UNIX?

2) Writing code in dialect when you don't need to make you sound like a hillbilly to people that speak the language. You are better off with DECIMAL(s,p) so you can use a properly sized column.

3) It does display and formatting in the back end, with commas and dollar signs. That defeats the purpose of a tiered architecture.

4) The MONEY data type has rounding errors.

Using more than one operation (multiplication or division) on money columns will produce severe rounding errors. A simple way to visualize money arithmetic is to place a ROUND() function calls after every operation. For example,

Amount = (Portion / total_amt) * gross_amt can be rewritten using money arithmetic as: Amount = ROUND(ROUND(Portion/total_amt, 4) * gross_amt, 4)

Rounding to four decimal places might not seem an issue, until the numbers you are using are greater than 10,000.

check here https://social.msdn.microsoft.com/Forums/sqlserver/en-US/de0e5cfe-b984-4700-b81f-a0478a65daf1/difference-between-numeric-and-money-data-type-in-sql-server?forum=transactsql

Jayanti Lal
  • 1,175
  • 6
  • 18
1

Money doesn't provide any advantages over Decimal. If fractional units up to 5 decimal places are not valid in your currency or database schema, just use Decimal with the appropriate precision and scale.

DECLARE 
@mon1 MONEY, 
@mon2 MONEY, 
@mon3 MONEY, 
@mon4 MONEY, 
@num1 Numeric(19,6), 
@num2 Numeric(19,6), 
@num3 Numeric(19,6), 
@num4 Numeric(19,6) 

SELECT 
@mon1 = 100, @mon2 = 339, @mon3 = 10000, 
@num1 = 100, @num2 = 339, @num3 = 10000 

SET @mon4 = @mon1/@mon2*@mon3 
SET @num4 = @num1/@num2*@num3 

SELECT @mon4 AS moneyresult, @num4 AS numericresult
theweeknd
  • 277
  • 1
  • 12
1

Your choice of the data types will be down to the precision required for the information in question. Even though, as the name suggests, the Money data type is designed for obvious reasons, it is limited to only four decimal places which is why the numeric data type is often favored.

aptalk
  • 11
  • 1