November 21, 2018 at 11:35 am
Jeff Moden - Wednesday, November 21, 2018 8:35 AMpatrickmcginnis59 10839 - Wednesday, November 21, 2018 6:47 AMfrederico_fonseca - Tuesday, November 20, 2018 12:43 PMjcelko212 32090 - Sunday, November 18, 2018 3:39 PMIf you deal with money and currencies, you know that anybody who storing currency data is floating-point is committing a crime under EU and GAAP regulations.kindly back this statement with links to the relevant EU legislation.
I'm going to give Joe a pass on this one (lol like he cares whether I give him a pass or not). I think its generally agreed not to store money in floats, unless you restrict the number to having no decimal points, and even then, why not just use integers if you're going to count pennies, etc
The closest I found to an actual regulation was this bit on the web:
I doubt that'll satisfy the folks who just want to catch Mr. Celko typing something wrong on the internet but at least the link chats about the subject.
Absolutely agreed on this one. That's why I asked Joe what the specs actually were (a reference link or two from him would be great) and what he used in such cases. Way too many people "format" the interim answers way too early in a series of calculations and then wonder why Granny with her 4 function calculator is able to catch "penny errors" on things like mortgage interest calculations, etc, . Add to that the silent automatic severe reduction in scale that can occur with the Decimal and Numeric datatypes (which some folks aren't aware of) in SQL Server and you can end up with some real problems.
https://docs.microsoft.com/en-us/sql/t-sql/data-types/precision-scale-and-length-transact-sql?view=sql-server-2017Of course, there are also problems with FLOAT datatypes, as well, and so it would be interesting and helpful if he let us know how he solves these types of problems, especially when it comes to meeting the requirements of EU and GAAP regulations.
Well I would think floats are out of the question for storing currency like Joe says. Obviously this assumes people have at least a bit of familiarity with numerical analysis topics when arranging for calculations but I wouldn't hold my breath on that.
November 21, 2018 at 1:22 pm
patrickmcginnis59 10839 - Wednesday, November 21, 2018 11:35 AMJeff Moden - Wednesday, November 21, 2018 8:35 AMpatrickmcginnis59 10839 - Wednesday, November 21, 2018 6:47 AMfrederico_fonseca - Tuesday, November 20, 2018 12:43 PMjcelko212 32090 - Sunday, November 18, 2018 3:39 PMIf you deal with money and currencies, you know that anybody who storing currency data is floating-point is committing a crime under EU and GAAP regulations.kindly back this statement with links to the relevant EU legislation.
I'm going to give Joe a pass on this one (lol like he cares whether I give him a pass or not). I think its generally agreed not to store money in floats, unless you restrict the number to having no decimal points, and even then, why not just use integers if you're going to count pennies, etc
The closest I found to an actual regulation was this bit on the web:
I doubt that'll satisfy the folks who just want to catch Mr. Celko typing something wrong on the internet but at least the link chats about the subject.
Absolutely agreed on this one. That's why I asked Joe what the specs actually were (a reference link or two from him would be great) and what he used in such cases. Way too many people "format" the interim answers way too early in a series of calculations and then wonder why Granny with her 4 function calculator is able to catch "penny errors" on things like mortgage interest calculations, etc, . Add to that the silent automatic severe reduction in scale that can occur with the Decimal and Numeric datatypes (which some folks aren't aware of) in SQL Server and you can end up with some real problems.
https://docs.microsoft.com/en-us/sql/t-sql/data-types/precision-scale-and-length-transact-sql?view=sql-server-2017Of course, there are also problems with FLOAT datatypes, as well, and so it would be interesting and helpful if he let us know how he solves these types of problems, especially when it comes to meeting the requirements of EU and GAAP regulations.
Well I would think floats are out of the question for storing currency like Joe says. Obviously this assumes people have at least a bit of familiarity with numerical analysis topics when arranging for calculations but I wouldn't hold my breath on that.
While I agree that there are issues with FLOATs vs currency, I would like to see the official spec where Joe says it's a "committing a crime under EU and GAAP regulations".
--Jeff Moden
Change is inevitable... Change for the better is not.
November 22, 2018 at 3:57 am
Jeff Moden - Wednesday, November 21, 2018 1:22 PMWhile I agree that there are issues with FLOATs vs currency, I would like to see the official spec where Joe says it's a "committing a crime under EU and GAAP regulations".
I do support this.
We should be aware of roundoff (and other problems) using dataformats see:
Deadly Rond-Off Error
(29 killed 'because' off Round-Off). **)
Short description of Numerical Errors.
***)
But, most people are guilty of using Spreadsheets for financial calculations, storage and desision making. Most Spreadsheats store numerical information in Floats. And for other formats there are problems as wel, for example 08:00 in the morning can not be represented exactly in a MS spreadsheet, it is stored in a float as 1/3 of the 'date' and as we know 1/3 can not be represented exactly in a binairy system. So most of us are 'guilty' :blush:
And then money (or integer) types can not store financial information if fractions of financial costs have to be represented. For example a 1000 items cost a specific amount of money and the amount for a single item is also stored in some database. These things happen.
So we should be aware of these types of problems. But there is no single solution.
Ben
**)
The 29 killed in the 1991 Dhahran attack was attrributed to the roundoff error. But this was caused by operating the Patriot system outside its operating parameters and it is not clear if the patriot would have disabled the scud.
***)
The roundoff error for the Ariane 5 failure. Before the accident there had been intensive studies which parameters should have which formats and risc calculations were done. But they where done for the Arianne 4 vehicle and some systems where not reassessed for the Arianne 5 and kept as they were. Regrettably this offending parameter was not reassessed and what was even worse for the Arianne 5 vehicle this was in an obsolete system, removing the system was considered a risc and therefore it remained. This resulted in the auto destruction of the Arianne 5, because the parameter went out of bounds.
November 30, 2018 at 7:26 am
ben.brugman - Tuesday, November 20, 2018 5:26 AMjcelko212 32090 - Sunday, November 18, 2018 3:39 PMSo anybody using a digital calculator for money and currencies is committing a crime? 😀:sick:
Rounding might actually be worth writing an article. Good idea! I haven't looked at the current rules in a while, so that would make me do some research. Actually, I've seen someone get in trouble for using a pocket calculator.
He rounded every item in a long list either up or down (I camber which direction), instead of varying it from item to item. Then he did his summation. The errors accumulated in one direction and messed up the final results. Suddenly, what had been done on the mainframe didn't match his calculations.
ToEven is also known as 'Banking Rules', it the default used in IEEE 754 floating-point standards, which is why it's the default in .NET.
Conversely, AwayFromZero is also known as 'Commercial Rounding'. I don't know why it is the default of SQL Server. It's very widely known and SQL has been used in commercial environments.
Euro conversion gets even weirder. If you go from one non-euro currency to another, you have to convert the first currency to euros, then convert the euros to the second currency. It's called triangulation. The problem is that transactions occur so fast that the rates can change during the calculations - ARRGH :sick:!
I'm going to assume by now everybody seen the problems with the old Sybase MONEY datatypes. Basically, if you do multiplication and division, it rounds too soon and only carries things out to four decimal places.
I worked on a project to convert a system back when the Euro came in and iirc, not only did you have to triangulate via the Euro, it was also specified that you had to do so to at least 6 decimal places.
Viewing 4 posts - 31 through 33 (of 33 total)
You must be logged in to reply to this topic. Login to reply