Are the posted questions getting worse?
-
GSquared (12/15/2010)
Kit G (12/15/2010)
Lynn Pettis (12/15/2010)
I'm not in a position to test, but don't you get the same issue when using decimal(19,4) as you do when usinf money?Now I may be wrong, but I think you don't have that much to worry about in normal accounting applications. The problem is more likely to surface when you start multiply and dividing money types which really doesn't make much sense to me.
I think the problem comes about if you start doing interest rates which will have multiplication in them and then have to figure out a monthly payment which will probably include some division. Not that i have tested it, but I seem to remember reading a thread/article on it where the errors in using MONEY for actual financial transactions was gone over. It was concluded that using DECIMAL was a much better solution and more likely to keep you out of fraud investigations. But this is from a somewhat dim memory noted as a curiosity as I don't deal with the MONEY data type at all.
I remember one from a couple of years ago (I think it was that long) with Matt Miller, Jeff, and a few others. I don't remember all the details, but I do remember Matt essentially proving that the money datatype will break normal banking applications, while decimal will work just fine. I think this was based on actual work for actual banks. Can't find the conversation now, but I do remember it.
Any chance this is what you were looking for?
http://www.sqlservercentral.com/Forums/Topic454501-373-1.aspx
-
Heh, and here I just dropped a line on the original article about what Jeff explains clearly in that link above. The division from decimal(x,x), at least on 2k5, goes to a float result... then multiplication then brings it back to a decimal 19,4, rounded.
It'll depend on the # of significant digits you want carried through the calculations.
- Craig FarrellNever stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA -
Dave Ballantyne (12/15/2010)
...Just the same old datatype issues 😉 forcing to a decimal type by adding '.00' corrects the issue
-- code removed (too much technical stuff in THE THREAD)
So you're saying it's all caused by data type precedence? (in SS2K5 Money is "ranked" 9, INT is 12 and DECIMAL is 8).
Now that explains most of it (if not everything).
It seems like the MONEY data type cannot be expanded/converted internally when used in a division operation (causing either rounding or truncation), but DECIMAL can.
I guess there is a valid reason for MS to provide the MONEY data type as it is. Does anybody know that reason? Right now it looks like the risk/benefit ratio is > 1... :unsure:
-
LutzM (12/15/2010)
Lynn Pettis (12/15/2010)
I'm not in a position to test, but don't you get the same issue when using decimal(19,4) as you do when usinf money?Now I may be wrong, but I think you don't have that much to worry about in normal accounting applications. The problem is more likely to surface when you start multiply and dividing money types which really doesn't make much sense to me.
Nope.
Here's the code:
DECLARE @m decimal(19,4)SET @m = 234.56
SELECT CAST((@m/1000)*1000 AS decimal(19,4))
-- result: 234.5600
As per the links provided this is not a bug but rather the implementation of some accounting rules. Anyone around with a deeper accounting background? (Mine is limited to receiving a check at the end of the month and estimating how many days will be left at the end of the money...)
Hey, hey, hey, keep that technical stuff out of The Thread
-
You tell 'em, Steve !!!
I wander in here for the first time in a month and get bombarded with utterly useful technical information.
Now I know how a groundhog feels when he sees his shadow:
Back to hibernation for me!
I'll try again next year.
P.S. Alvin, good luck with the Hula-Hoop thing. Maybe this year will be the year.
__________________________________________________
Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills -
LutzM (12/15/2010)
Regarding the money data type:Here's an interesting example I just found here
-- code removed (too much technical stuff in THE THREAD)Another interesting article can be found here at SSC
HAHAHAHA
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events -
The Dixie Flatline (12/15/2010)
You tell 'em, Steve !!!I wander in here for the first time in a month and get bombarded with utterly useful technical information.
Now I know how a groundhog feels when he sees his shadow:
Back to hibernation for me!
I'll try again next year.
P.S. Alvin, good luck with the Hula-Hoop thing. Maybe this year will be the year.
Should be start calling you Punxsutawney Flatline or Dixe Groundhog?
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events -
CirquedeSQLeil (12/14/2010)
GSquared (12/14/2010)
CirquedeSQLeil (12/14/2010)
GSquared (12/14/2010)
Alvin Ramard (12/14/2010)
GSquared (12/14/2010)
Alvin Ramard (12/14/2010)
I'd prefer a picture of the horse stepping on the iPad. 😀How about a horse stepping on an iMat?
...
iMat? You'll need DBCC TimeWarp for that one. 😛
Not for a picture of it!
Where's the iBed?
The iEgo won't allow anything too risque in the app store. Do you really think they'll allow anything that would allow you to ... oh never mind ... this is a family-friendly forum, can't go where I was about to.
What's so bad about going to bed? 😉
It depends on whether or not the horse is involved. 😀
--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) -
CirquedeSQLeil (12/14/2010)
Alvin Ramard (12/14/2010)
CirquedeSQLeil (12/14/2010)
Does this frighten anybody else?All I see is a red x, so, no, it doesn't frighten me. 😛
Hmmm - lemme see
It depends... is that the front or the back? :hehe:
--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) -
Steve Jones - SSC Editor (12/14/2010)
Is it now more, or less, frightening?It depends... where's your other hand? 🙂
--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) -
CirquedeSQLeil (12/14/2010)
Brandie Tarvin (12/14/2010)
CirquedeSQLeil (12/14/2010)
A Christmas fools joke for the officeWow. That is truly evil. I don't think that would be appreciated at my workplace.
I got a co-worker with that this morning. He was stumped for about 5 minutes. Rebooted and was still having problems. Buahahaha. He finally found it - and the joke is still great hours later.
Ah... no... if someone did that to me and it caused any harm to any data I was working on, they'd find the crushed remains of the device on the floor of their car... you know... the one I filled with cement? 😉
--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) -
jcrawf02 (12/15/2010)
But on the upside, you don't care that you're dead.Heh... I was going to say the same thing about anyone that causes me to lose or corrupt data with that bloody jokester fob. 😉
--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) -
My second ever QotD is being published tomorrow. Can you'll let me know what you think of it please?
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!
Links:
For better assistance in answering your questions
Performance Problems
Common date/time routines
Understanding and Using APPLY Part 1 & Part 2 -
WayneS (12/15/2010)
My second ever QotD is being published tomorrow. Can you'll let me know what you think of it please?Far too easy
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events -
Brandie Tarvin (12/15/2010)
I think I just found it. Money rounds funny when being used in division and multiplication.Interesting that I never ran into this before. I should make a SQL Server question out of this...
It's not the Money datatype that's responsible for that problem. Any 4 decimal place datatype would have such a problem. Most handheld calculators calculate to 15 decimal places and will only display up to 13.
--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)
Viewing 15 posts - 22,516 through 22,530 (of 66,712 total)
You must be logged in to reply to this topic. Login to reply