Decimal/float/etc.

Question

Decimal/float/etc.

ryan mcneill

SSC Veteran

Points: 223
More actions
July 11, 2006 at 1:15 pm

#169104

Can someone explain to me whether it's best to use decimal or float when dealing with monetary amounts (no dollar sign, btw).
Also, for some reason, the person who created the table made the amount field "varchar." Can I just go into design view and change it to decimal, float, etc.?

Viewing 11 posts - 1 through 11 (of 11 total)

You must be logged in to reply to this topic. Login to reply

Gift Peddie SSC Guru Points: 73570 More actions · Answer 1

Float is usually used for money but converted to Decimal or Numeric to persist because in SQL Server you can set precision and scale in Decimal or Numeric and not Float. If you see Float used it is because most ANSI SQL Math functions require Float data type to give any result. You can change in design view if not use ANSI SQL ALTER TABLE to change the column. If you are in SQL Server 7.0 use Numeric instead of Decimal because in SQL Server 7.0 Decimal and Money will give you less than a Dollar in Web applications. Hope this helps.

Kind regards,

Gift Peddie

Kind regards,
Gift Peddie

PW-201837 SSC-Insane Points: 20805 More actions · Answer 2

This is why you don't use float for monetary amounts:

Select cast(100.00 As float) As IHave100Bucks

Select cast(100.01 As float) As IDeposited1CentToMyAccount

Lowell SSC Guru Points: 323546 More actions · Answer 3

nice, concise example to show someone why float is not a good solution for moeny. Thanks!

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Jeff Moden SSC Guru Points: 1004638 More actions · Answer 4

PW beat me to it... don't use float for anything, if you can help it. It's one of those "approximation" data types that are based on binary math.

Now, what to use for money calculations.... ummmmm .... errr.... how'z 'bout the MONEY datatype? It can even be converted to a VARCHAR with commas, if you need them.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Gift Peddie SSC Guru Points: 73570 More actions · Answer 5

That is not correct you have to use Float if your application need math functions in most RDBMS(relational database management systems) MySQL included and not just for binary math. Let say you want to get random users from SQL Server you us Float because Float is required to use the handy RAND function. The general advice convert Float to Decimal to persist. And no you don't use Money because if your web application interop with other platforms Money will give you less than one dollar it is a known issue since 1999 in SQL Server 7.0. I have also answered the question last year about Money rounding to less than a Dollar. Try the link for the SQL Server Math functions and why you cannot avoid Float but just convert it to persist. Hope this helps.

http://msdn2.microsoft.com/en-US/library/ms177516.aspx

Kind regards,

Gift Peddie

Kind regards,
Gift Peddie

Jeff Moden SSC Guru Points: 1004638 More actions · Answer 6

Excellent reference, GP... though I'm not sure what you meant by "you have to use Float if your application need math functions". If you meant that you are inherently using Float when you use a math function, I agree... but that doesn't mean you have to store data in the table that way nor do you have to explicity convert data to float when using a function. Considering the approximation errors built into FLOAT, I wonder which one of these actually has the correct answer... the difference is very small but if accuracy counts...

DECLARE @MyValue DECIMAL(38,20)

SET @MyValue = .1

DECLARE @MyExponent DECIMAL(38,20)

SET @MyExponent = .1

SELECT POWER(@MyValue,@MyExponent)

GO

DECLARE @MyValue FLOAT

SET @MyValue = .1

DECLARE @MyExponent FLOAT

SET @MyExponent = .1

SELECT POWER(@MyValue,@MyExponent)

As PW demonstrated, saving a known amount in the table and having it come back in an approximate depiction of the number because of the FLOAT inherent approximation error, is at best, unnerving. Then, there's also a "tolerance buildup of approximations" with FLOAT that can cause an incorrect answer over the long haul... DECIMAL doesn't have that problem. It's not much of an error but, still, it's a computational error...

DECLARE @MyTemp TABLE (SomeFloat FLOAT, SomeDec DECIMAL(38,15))

INSERT INTO @MyTemp (SomeFloat,SomeDec)

SELECT TOP 1000000

.1 AS SomeFloat,

.1 AS SomeDec

FROM Master.dbo.SysColumns sc1,

Master.dbo.SysColumns

SELECT SUM(SomeFloat) AS [SUM(SomeFloat)],

SUM(SomeDec) AS [SUM(SomeDec)]

FROM @MyTemp

Didn't know about the MONEY datatype problem with web apps because the web developer's I've worked with have always taken the time to make CRUD and result-set stored procedures for their I/O... they must do the conversions in those so the problem never rears it's ugly head. Do you have any links on that particular subject (the MONEY problem on web apps)? I sure would like to learn more about that problem.

So, I guess my position stands... you don't need FLOAT and I recommend that you don't explicity use the FLOAT datatype (the ONLY exception is in conjunction with DATETIME which is also inherently a FLOAT).

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Gift Peddie SSC Guru Points: 73570 More actions · Answer 7

The first time I saw Money's problems was 1999-2000 SQL Server 7.0 with Coldfusion front end Money was averaging 96-98 cents on the dollar, and the person was given less than one week to fix it be fired, Decimal did not work Numeric fixed it. Now I work for Big Pharma in Lab Informatics Float is not an option it is a way of life and I covered the just use it to calculate but use Decimal as the column data type in my first post in this thread. Yesterday I just came across something new money rounding different during different sessions. And the second link is the function Power it will only accept Float or a type that can be implicitly converted to Float in SQL Server that is Real less than two minutes after changing to Float the math was solved. I don't know I about you I think Float use should be managed and not avoided. If your employer only use arithmetic fine but if you use Geometry, Advanced Algebra and Calculus you will learn to manage Float. Hope this helps.

http://forums.asp.net/thread/1276450.aspx

http://forums.asp.net/thread/1337272.aspx

Kind regards,

Gift Peddie

Kind regards,
Gift Peddie

Jeff Moden SSC Guru Points: 1004638 More actions · Answer 8

I see why people come to the conclusion that you must use FLOAT as an input to functions... what people should say on funtions is that you must not use an INTEGER related datatype if you want to get an answer (instead of saying is must be float).

I appreciate your suggestions about the use of FLOAT but it's approximations frequently give me incorrect answers for some of the more complicated calculations we do with the help of functions like POWER and SIN/COS. Like I said before, the errors are very tiny but are still errors none-the-less. I've never had a problem with MONEY giving me 98 cents on the dollar, either...

I talked with the GUI guys, too. They recognized very early on that SQL Server has some propriety associated with some of it's data types and they do resolve those with interface stored procs... they said that's the right way to do it, anyway, so if they need to change the interface, they don't need to recompile their app code. Apparently, some of the larger software companies are aware of that... we've never had the kinds of problems you describe with PeopleSoft or a couple of the other majors.

Anyway, thanks for the tips...

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

David Burrows SSC Guru Points: 65144 More actions · Answer 9

I have used both money rounded to 2 dec places and numeric to 2 dec places to hold money. Nowadays I prefer to use numeric so that it is clear how decimal places there are. If I need formatting (although I normally leave it to the presentation app) then I cast it to money and then convert it for formatting.

btw numeric and decimal are synonymous and functionally equivalent.

Far away is close at hand in the images of elsewhere.
Anon.

Gift Peddie SSC Guru Points: 73570 More actions · Answer 10

That is correct Numeric and Decimal is like Real and Float.

Kind regards,

Gift Peddie

Kind regards,
Gift Peddie