Page 1 of 3 123 LastLast
Results 1 to 15 of 33
  1. #1
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002

    decimal positions

    hoping PatP reads this thread, as i'm sure i got this information from him

    for accounting apps, how many decimal positions do you store? DECIMAL(11,2) or DECIMAL (13,4)?

    i've mentioned (on another forum) what i ~thought~ i heard PatP say long ago, that GAAP recommends 4 decimal positions

    i've been asked for a citation, and of course googling turns up squadoosh...
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  2. #2
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Personally, I think it is 4 - but this is based on the fact that mssql money datatype stores 4dp accuracy.
    George
    Home | Blog

  3. #3
    Join Date
    Jun 2007
    Location
    London
    Posts
    2,527
    Sybase has a money type and I pulled this from the manual -
    The money and smallmoney types store four digits to the right of the decimal point, but round up to the nearest hundredth (.01) for display purposes. When data is converted to a money type, it is rounded up to four decimal places.
    Mike

  4. #4
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Yeah, Rudy. Why not just use the MONEY datatype?
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  5. #5
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    as you get older, you lose 3 things
    1. your hearing
    2. your memory
    3. ...
    i forget what the third one is
    and i forget where i've seen advice never to use MONEY datatype
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  6. #6
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    MONEY is the root of all evil datatypes.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  7. #7
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Due to a change in employment, I no longer have easy access to the GAAP Standards Manual. I'll see what I can do to provide an explicit citation, but that will definitely be a challenge right now.

    GAAP does explicitly call for four decimal places below the decimal point (as in 0.0001) for storage (but not necessarily display) of detail currency values. The purpose for the specification is complex, but the primary reason is to permit auditing of the computation processes involved in the production of Audited financial statements (if there are less than four decimal positions used anywhere in the computation of financials, a caveat is required in the Audit Opinion document).

    The number of decimal positions that GAAP requires to the left of the decimal place is a bit harder to concretely identify. A rule of thumb that will cover all of the cases in the specifiication is that currency values need to be able to represent values one hundred times the largest value (including totals) that are directly based on the value. This means that if your general ledger totals to a million dollars, then the rule of thumb requires that all entries need to be able to represent one hundred million dollars). In most cases this is overkill, and the underlying rules are FAR more complex (around five pages of fine print), but this rule of thumb covers all cases for the rules.

    I'll try to get a citation for you, but the GAAP Standards Manual is very hard to access. I believe that it can only be sold to Licensed CPAs and that they are contracturally committed to rules of conduct for handling the documents. That makes the GAAP Standards Manuals only slightly easier to access than CIA Technical Manuals. Specifically I don't know anywhere that they appear online: public, private, or otherwise.

    -PatP

  8. #8
    Join Date
    Jun 2007
    Location
    London
    Posts
    2,527
    Quote Originally Posted by Pat Phelan
    The number of decimal positions that GAAP requires to the left of the decimal place is a bit harder to concretely identify. A rule of thumb that will cover all of the cases in the specifiication is that currency values need to be able to represent values one hundred times the largest value (including totals) that are directly based on the value.
    Interestingly the current run on Zimbabwe Dollars has caused problems on a few of our internal applications. I'm guessing they might want to change that 100 figure pretty soon. A recent article on the run stated

    At the time of writing this article, US$1 was worth $7 billion on the open market and the rate to the pound sterling was at $16 billion.
    Scary

    Mike

  9. #9
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Quote Originally Posted by blindman
    MONEY is the root of all evil datatypes.
    I thought that non-deterministic datatypes were the root of all evil?
    George
    Home | Blog

  10. #10
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by Pat Phelan
    [relevant stuff]
    pat, i love u

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  11. #11
    Join Date
    Jun 2007
    Location
    London
    Posts
    2,527
    Quote Originally Posted by georgev
    I thought that non-deterministic datatypes were the root of all evil?
    Can you explain the term with a few simple examples - I was too thick to understand the white papers that google returned

  12. #12
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    non-deterministic = approximate. E.g. float
    George
    Home | Blog

  13. #13
    Join Date
    Jun 2007
    Location
    London
    Posts
    2,527
    I'm still confused - I thought deterministic meant you always got the same result for the same supplied values. So the following Sybase functions are example is deterministic i.e. produce the same result each time:
    Code:
    select round( 123.456,1 ) -> 123.5

    While the following functions are non-deterministic as the results will change each time you run them:
    Code:
    select rand()
    select getdate()

    Oddly though, if you supply a seed to the rand function then that suddenly changes it to deterministic. I then tried to apply all this to types to get a non-deterministic type but couldn’t. I'll agree that a float is an approximate value ie 1/3.0 will produce 0.333333 rather than a true 1/3 but it will always produce 0.333333. Was the issue more to do with the fact that internal size of a money type might vary between systems?

    Mike

  14. #14
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Mike:

    I think that the issue that George was trying to explain was the fundamental difference between counting types and measuring types of numeric values. Unfortunatly if that is what he meant, MONEY actually IS a counting type so I'm confused by his comment.

    Counting numeric types are ones that store how many of something. Counting types are exact, no rounding errors are inherant in the type itself so repeated operations on the largest and smallest possible values will always produce consistent results (no loss of precision). For most SQL databases these types are based on either integers or decimal data types.

    Measuring numeric types are ones that store how much of something. They don't need to be exact, just fast, flexible, and precise within specified limits. Repeated operations on these types may or may not be consistent due to the acceptable "floating point error" which can accumulate, as well as the potential for slightly different computational results due to hardware differences. For most SQL databases, these types are based on floating point numbers.

    From the mathematical purists point of view, counting types are truly deterministic because they will always produce the same values for the same computation. Numeric types are not necessarily deterministic since some operations may be meaningless (for example adding .01 to C which is rougly 3e8 generally results in a value equivalent to C).

    If that isn't what George was getting at, I'm sure that he'll pipe up!

    -PatP

  15. #15
    Join Date
    Jun 2007
    Location
    London
    Posts
    2,527
    Quote Originally Posted by Pat Phelan
    From the mathematical purists point of view, counting types are truly deterministic because they will always produce the same values for the same computation. Numeric types are not necessarily deterministic since some operations may be meaningless (for example adding .01 to C which is rougly 3e8 generally results in a value equivalent to C).
    A good description as always Pat but I still think determistic is getting mixed with approximate in the quote above. Deterministic should mean that the results are pre-determined by the inputs. So if 3e8 + 0.1 produces 3e8 every time it is run then doesn't that still make it deterministic? It's not a big point at all and I may well be wrong.

    Mike

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •