datatype for prices

[wallm]

I have a database field that is currently money datatype.

what's the best datatype to use to store product prices? Is it double or money or decimal and any reasons why.

At the moment I am using the money datatype but there's 4 decimal places such as 10.0000

thanks.

 

[SQLDenis]

Money is a dangerous proprietary Garbage (with a capital G) datatype and should never be used

run this to see what I mean

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


Result: 


money_result          numeric_result 
--------------------- --------------------- 
2949.0000             2949.8525

do you see what happens to the precision?

Denis The SQL Menace
SQL blog:

http://sqlservercode.blogspot.com/

Personal Blog:

http://otherthingsnow.blogspot.com/

 

[wallm]

ok that's good advice, I'll switch it over to double. that should work shouldn't it.

 

[SQLDenis]

double is not a SQL server data type

available data types are:

bigint Binary bit char cursor
datetime Decimal float image int
money Nchar ntext nvarchar real
smalldatetime Smallint smallmoney text timestamp
tinyint Varbinary Varchar uniqueidentifier

you should be using decimal or numeric


Denis The SQL Menace
SQL blog:

http://sqlservercode.blogspot.com/

Personal Blog:

http://otherthingsnow.blogspot.com/

 

[wallm]

oops sorry I meant decimal.