Feedback needed on using decimal vs float for money fields 4

[jchewsmith]

I am just looking for some opinions on best practices involving prices. I am looking to create a database for sales with unit prices that go to 4 decimal places but I want to be able to round to 2 decimal places on all other number fields.

I would love the rounding to follow the 4/5 rule if possible?

 

[gmmastros]

I would absolutely go with decimals for your data types. There is a Money data type, but there are problems with using that data type. Float is an approximate data type, so that is an even worse choice. A buddy of mine works for a very large financial institution and they use decimals for their data.

I am not sure what you mean by following the 4/5 rule. SQL server has a round function that is most accurately defined as "Round away from zero".

Declare @value Decimal(10,5)

Set @Value = 10.12345;

Select @Value, Round(@Value, 4);

Set @Value = -10.12345;
Select @Value, Round(@Value, 4);

Notice how the negative value get's more negative. Put another way, the rounded number is less than the un-rounded number. It sounds to me like you want to round up. If so, take a look at this blog I wrote several years ago.

http://blogs.lessthandot.com/index.php/datamgmt/datadesign/sql-server-rounding-methods/

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
twitter
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[djj55]

George, what are the problems with the MONEY data type?

djj
The Lord is my shepherd (Psalm 23) - I need someone to lead me!

 

[gmmastros]

Problems with the money data type occur when performing math operations. (add and subtract are ok).

Take a look at this:

DECLARE
    @mon1 MONEY,
    @mon2 MONEY,
    @mon3 MONEY,
    @mon4 MONEY,
    @num1 DECIMAL(19,4),
    @num2 DECIMAL(19,4),
    @num3 DECIMAL(19,4),
    @num4 DECIMAL(19,4)
 
    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

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
twitter
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[djj55]

Thanks George, I will have to try and remember that.

djj
The Lord is my shepherd (Psalm 23) - I need someone to lead me!