January 16, 2020 at 3:22 pm
Actually, MONEY is useful if only for documentation purposes. You could use decimal but why would you want to? Being an integer makes the type faster than decimal.
DATETIME does have a few rough edges, but is very useful. What objection do you have to it?
Well, the issue with MONEY is that who's to say monetary amounts should always have 4 decimals? Often it's too much, and sometimes it's not enough. And if you start multiplying them, accuracy goes out the window.
DATETIME takes the prize for the wonderful resolution of 1/300 of a second, and the inability to not have fractions. Also, back when it was the only available temporal data type, it used to drive me insane. Particularly the way you'd use it to store pure dates, only to have the time of day sneak in from some ill-placed GETDATE().
January 16, 2020 at 3:26 pm
Well, the issue with MONEY is that who's to say monetary amounts should always have 4 decimals? Often it's too much, and sometimes it's not enough. And if you start multiplying them, accuracy goes out the window.
I think there are accounting standards that say 4 d.p. should be used for money calculations.
January 16, 2020 at 3:37 pm
Jonathan AC Roberts wrote:I think there are accounting standards that say 4 d.p. should be used for money calculations.
Well, that would depend on your country of operation, wouldn't it?
I don't know, I'm not an accountant, but I think for currencies that have cents or pence then this would be a general standard to maintain enough accuracy in the calculations.
January 16, 2020 at 4:02 pm
Possibly, but then again, we all know how much accountants love guesswork, right 😀
Anyway, I could easily dig up examples from the EU where the conversion rates between the EURO currencies have to be expressed with 6 significant digits, which in most cases means 5 or 6 decimal places. If you're only trading in a single currency, it might not be that big an issue. Until it is, of course...
January 16, 2020 at 4:40 pm
I remember seeing the 4 decimal standard on commission/interest tracking purposes, so that you can limit the rounding error issues. when you're accumulating x % on a daily basis for a year, you will end up with fairly distinct issues if you keep dropping out anything after 2 places on a daily basis.
Not matter what the specific data type, in general you need to store MORE decimal places than the # you need to report, so that the # of significant digits match up to the expectation.
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
January 16, 2020 at 7:30 pm
Yes, because MONEY is a precise data type with 4 decimal places. It's implemented as a 64-bit integer, scaled down by 10000. That and DATETIME must be the two lamest data types in SQL Server (apart from SMALLDATETIME and SMALLMONEY, of course. They were conceived by nincompoops, in my hones opinion!)
Opinion noted... However, I can more easily do things with DATETIME that can only be dreamed of with DATETIME2. Even Microsoft realized their mistake but, instead of fixing it, the patched it by creating DATEDIFF_BIG.
--Jeff Moden
Change is inevitable... Change for the better is not.
January 16, 2020 at 8:07 pm
Opinion noted... However, I can more easily do things with DATETIME that can only be dreamed of with DATETIME2. Even Microsoft realized their mistake but, instead of fixing it, the patched it by creating DATEDIFF_BIG.
In full agreement there. Where, oh where is the TIMESPAN data type?
January 17, 2020 at 8:45 am
Nice question and really interesting discussion, thanks all
____________________________________________
Space, the final frontier? not any more...
All limits henceforth are self-imposed.
“libera tute vulgaris ex”
January 17, 2020 at 8:46 pm
>> Well, the issue with MONEY is that who's to say monetary amounts should always have 4 decimals? <<
GAAP (generally accepted accounting practices) in the US and some EU regulations. There were also rules about rounding, multiplying and dividing monetary amounts. I also think it's gone up to five decimal places. Now, but I have to look to be sure.
>> And if you start multiplying them, accuracy goes out the window. <<
It's really worse than that; the MONEY data types do the rounding incorrectly! This means that divisions and multiplications are worse than decimal data types. I don't have it immediately available, but there is a classic example of this. It was posted in the forums for the past decades.
Please post DDL and follow ANSI/ISO standards when asking for help.
Viewing 10 posts - 16 through 24 (of 24 total)
You must be logged in to reply to this topic. Login to reply