December 15, 2010 at 12:28 pm
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
December 15, 2010 at 12:44 pm
LutzM (12/15/2010)
Regarding the money data type:Here's an interesting example I just found here
DECLARE @m MONEYSET @m = 234.56
SELECT CAST((@m/1000)*1000 AS MONEY)
Another interesting article can be found here at SSC
Awesome - thanks Lutz.
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
December 15, 2010 at 12:47 pm
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.
December 15, 2010 at 12:51 pm
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.
Using money yields 234.50
Using decimal(19, 4) yields 234.56
Using decimal(19, 2) also yields 234.56
This is not good! :w00t:
For best practices on asking questions, please read the following article: Forum Etiquette: How to post data/code on a forum to get the best help[/url]
December 15, 2010 at 12:53 pm
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:
-- code removed (too much technical stuff in THE THREAD)
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...)
December 15, 2010 at 1:31 pm
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.
-- Kit
December 15, 2010 at 1:35 pm
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...)
Just the same old datatype issues 😉 forcing to a decimal type by adding '.00' corrects the issue
DECLARE @m MONEY
SET @m = 234.56
SELECT CAST((@m/1000.00)*1000.00 AS MONEY)
December 15, 2010 at 1:38 pm
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.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
December 15, 2010 at 1:41 pm
Dave Ballantyne (12/15/2010)
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...)
Just the same old datatype issues 😉 forcing to a decimal type by adding '.00' corrects the issue
DECLARE @m MONEY
SET @m = 234.56
SELECT CAST((@m/1000.00)*1000.00 AS MONEY)
There's still an issue in that money, in some circumstances, does not yield the same results as decimal(x,2). The comment was made regarding a connection with accounting rules. I'm guessing those rules state that you always drop the fraction of a cent.
For best practices on asking questions, please read the following article: Forum Etiquette: How to post data/code on a forum to get the best help[/url]
December 15, 2010 at 1:47 pm
No. It's because Money only goes to four decimal places.
If you simply divide @m by 1000 (in the sample provided), you'll see it cut off the final digit, because it only allows four after the decimal place.
Try it with various Decimal formats, and you'll find that the sub-calculation will keep all the digits, and usually pad it with a bunch of zeroes after them too.
It's not an accounting rule, it's what the data type can hold after the decimal place.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
December 15, 2010 at 1:49 pm
Dave Ballantyne (12/15/2010)
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...)
Just the same old datatype issues 😉 forcing to a decimal type by adding '.00' corrects the issue
DECLARE @m MONEY
SET @m = 234.56
SELECT CAST((@m/1000.00)*1000.00 AS MONEY)
I'm being thick right now so please forgive. But why are we multiplying by 1000 within the cast right after dividing by 1000 in the cast? If you move the multiplication to outside the cast then the results differ and are more appropriate to the expected outcomes.
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
December 15, 2010 at 1:53 pm
CirquedeSQLeil (12/15/2010)
Dave Ballantyne (12/15/2010)
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...)
Just the same old datatype issues 😉 forcing to a decimal type by adding '.00' corrects the issue
DECLARE @m MONEY
SET @m = 234.56
SELECT CAST((@m/1000.00)*1000.00 AS MONEY)
I'm being thick right now so please forgive. But why are we multiplying by 1000 within the cast right after dividing by 1000 in the cast? If you move the multiplication to outside the cast then the results differ and are more appropriate to the expected outcomes.
Get rid of the CAST, and you still get the same results.
This issue is that MONEY does not obey the same rules as DECIMAL(x, 4) or DECIMAL(x, 2).
For best practices on asking questions, please read the following article: Forum Etiquette: How to post data/code on a forum to get the best help[/url]
December 15, 2010 at 1:59 pm
Alvin Ramard (12/15/2010)
Get rid of the CAST, and you still get the same results.This issue is that MONEY does not obey the same rules as DECIMAL(x, 4) or DECIMAL(x, 2).
I think its all going wrong with the datatype that sqlserver is deciding that the result of the divisor.
DECLARE @m MONEY
SET @m = 234.56
SELECT @m/1000 as m1 into m1
go
DECLARE @m decimal(19,4)
SET @m = 234.56
SELECT @m/1000 as m2 into m2
Take a look at the types of cols in m1 and m2...
December 15, 2010 at 2:03 pm
Dave Ballantyne (12/15/2010)
Alvin Ramard (12/15/2010)
Get rid of the CAST, and you still get the same results.This issue is that MONEY does not obey the same rules as DECIMAL(x, 4) or DECIMAL(x, 2).
I think its all going wrong with the datatype that sqlserver is deciding that the result of the divisor.
DECLARE @m MONEY
SET @m = 234.56
SELECT @m/1000 as m1 into m1
go
DECLARE @m decimal(19,4)
SET @m = 234.56
SELECT @m/1000 as m2 into m2
Take a look at the types of cols in m1 and m2...
Interesting. That brings us back to what Gus was saying about the padding.
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
December 15, 2010 at 2:04 pm
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.
I recall this conversation as well. I haven't used MONEY since.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
Viewing 15 posts - 22,501 through 22,515 (of 66,712 total)
You must be logged in to reply to this topic. Login to reply