Professional Web Applications Themes

Which data type decimal, money, float...? - Microsoft SQL / MS SQL Server

For a sql server 2000 table that must hold exchange rates for currency and fractions of hours that will be multiplied by currency values, is it (in general) best to use a decimal data type or a smallmoney / money data type? The values are small, i.e. a typical exchange rate might be 1.3367 or a fraction of hours might be 1.5, 1.25 etc. The calculations will typically be done in a query or stored procedure Problem is that it's used for financial calculations and I'm not sure of the implications of money versus decimal. (I think float is completely ...

  1. #1

    Default Which data type decimal, money, float...?

    For a sql server 2000 table that must hold exchange rates for currency and
    fractions of hours that will be multiplied by currency values, is it (in
    general) best to use a decimal data type or a smallmoney / money data type?

    The values are small, i.e. a typical exchange rate might be 1.3367 or a
    fraction of hours might be 1.5, 1.25 etc. The calculations will typically
    be done in a query or stored procedure

    Problem is that it's used for financial calculations and I'm not sure of the
    implications of money versus decimal.

    (I think float is completely out due to rounding errors and approximation)

    I guess what I'm getting at specifically is, if I use a decimal type let's
    say 1.33567 in a calculation in a query or a stored procedure, what happens
    if the result of the calculation is 999,999,999.9999999999? Is it an
    overflow because one of the values is a decimal type of only 5 significant
    digits, or is the result independant of the data types used in the
    calculation?


    JCardinal Guest

  2. #2

    Default Re: Which data type decimal, money, float...?

    Both money and decimal are exact data types, so they are preferable to float
    in financial applications, as you correctly stated. Between them, it is a
    matter of taste. I prefer decimal, because money includes presentation of
    the data, and I like to have the data and the presentation of the data
    separated.

    --
    Dejan Sarka, SQL Server MVP
    FAQ from Neil & others at: http://www.sqlserverfaq.com
    Please reply only to the newsgroups.
    PASS - the definitive, global community
    for SQL Server professionals - http://www.sqlpass.org

    "JCardinal" <com> wrote in message
    news:phx.gbl... 
    type? 
    the 
    happens 


    Dejan Guest

  3. #3

    Default Re: Which data type decimal, money, float...?

    IMO, for exchange rates, you should not use money, because these are not
    a money amounts. So for that I would choose decimal.

    For currency values you could choose (small)money or decimal.

    As for the overflow question: if the result of a calculation is
    (implicitely or explicitely) converted to a decimal or money datatype,
    then it should fit in its definition. The least significant digits will
    be rounded if the money/decimal data type has lower precision.

    Gert-Jan


    JCardinal wrote: 
    Gert-Jan Guest

Similar Threads

  1. #39680 [NEW]: Type Casting from float into int
    By diefans at googlemail dot com in forum PHP Bugs
    Replies: 4
    Last Post: November 29th, 01:40 PM
  2. #8097 [Com]: printf() and float data vs.setlocale()
    By essen at dev-extend dot eu in forum PHP Bugs
    Replies: 0
    Last Post: November 29th, 09:30 AM
  3. Converting from data type varchar to data type money
    By schaudry in forum Coldfusion - Advanced Techniques
    Replies: 6
    Last Post: June 10th, 11:01 PM
  4. Converting data type varchar to data type money
    By schaudry in forum Coldfusion - Getting Started
    Replies: 1
    Last Post: June 10th, 07:22 PM

Bookmarks

Posting Permissions

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

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139