DOUBLE v DECIMAL

[Andy Trezise]

I'm pretty new to SQL and am a little confused as to which field type I
should use to store my 'currency' type data.

What's the main difference between these two data types and which would best
fit the purpose?

I want to store signed numerical data with up to 6 decimal places.

Thanks in advance for any help.

[Russell Fields]

Andy

DOUBLE is a floating point number and is, therefore, not precise. Use
DECIMAL (or NUMERIC). For example, you might use:
DECIMAL (19,6) which has 14 digits before the decimal and 6 digits
afterward and is stored in 9 bytes.

Read about DECIMAL and storage considerations:
http://msdn.microsoft.com/en-us/library/ms187746.aspx
Also data type conversions:
http://msdn.microsoft.com/en-us/library/ms191530.aspx

RLF

"Andy Trezise" <an...@trezise.f2s.com> wrote in message
news:uTnMZZtc...@TK2MSFTNGP06.phx.gbl...

[Rick Byham, MSFT]

Also consider using money. It only has 4 digits to the right of the decimal,
but it conforms to the "generally accepted accounting principles" for
handling money. But if you have special needs, by all means use numeric.
--
Rick Byham, MSFT
(Implies no warranty or rights)

"Russell Fields" <russel...@nomail.com> wrote in message
news:%23NaRost...@TK2MSFTNGP05.phx.gbl...

[Tom Cooper]

I would avoid using the money datatype. Not only is it proprietary to
Microsoft where decimal is ANSI standard. Money datatype has calculation
problems. Try running

declare @m money
declare @d decimal(9,2)

set @m = 234.56
set @d = 234.56

select cast((@m/1000)*1000 As money)
select cast((@d/1000)*1000 As decimal(9,2))

Note that the result of the calcuation using money type returns the value
234.50 NOT 234.56.

Tom

"Rick Byham, MSFT" <ric...@microsoft.com> wrote in message
news:eOlkfi3c...@TK2MSFTNGP06.phx.gbl...

[Rick Byham, MSFT]

Let's make a slight change to your example.

declare @m money
declare @d decimal(9,2)

set @m = 234.56
set @d = 234.56

select cast((@m/1000) As money) AS UsingMoney
select cast((@d/1000) As decimal(9,2)) AS UsingDecimal

Result:
UsingMoney
0.2345

UsingDecimal
0.23

You just walked away with 0.0045 dollars. Run this transaction a million
times and you'll wind up in jail. That's why generally accepted accounting
principles defined money with 4 decimal points. The money data type doesn't
have math problems, it just follows a specific set of accounting rules.Which
may or may not apply to your specific use.
Disclaimer - I got my finance degree in 1973. Things may have changed a lot
since then.

--
Rick Byham, MSFT
(Implies no warranty or rights)

"Tom Cooper" <tomc...@comcast.net> wrote in message
news:ue%2327y4c...@TK2MSFTNGP04.phx.gbl...

[Tom Cooper]

Yes, I agree. If you are concerned about keeping 4 digits to the right of
the decimal, you should not use decimal(9,2). You would, of course, use
decimal(x,4) where x was the value that gave you the required number of
digits to the right of the decimal. For example, if you needed 7 digits to
the left of the decimal, you could use decimal(11,4).

Pointing out that if you put .2345 into a decimal(9,2) column or variable,
you will get .23 is equivalent to pointing out that if you put "HELP" into a
char(2) variable you get "HE". It's true, but, IMO, not helpful. The fix
is not to give up on type CHAR datatype, it's to use CHAR(4) or VARCHAR(4)
or the Unicode (NCHAR or NVARCHAR), as needed for the values you need to
store.

But there is no way to fix the calculation error with the MONEY datatype
unless you first cast the MONEY as a decimal before doing the calculation.
And if you are going to do that, you might as well have used decimal in the
first place.

Tom

"Rick Byham, MSFT" <ric...@microsoft.com> wrote in message

news:OGcyQa6c...@TK2MSFTNGP06.phx.gbl...

[Rick Byham, MSFT]

My only quibble, is that you again describe the money math as an error. It's
not an error. It's following an accounting rule.

--
Rick Byham, MSFT
(Implies no warranty or rights)

"Tom Cooper" <tomc...@comcast.net> wrote in message

news:urYuHe9c...@TK2MSFTNGP06.phx.gbl...