Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

FLOAT vs DECIMAL.. Expand / Collapse
Author
Message
Posted Thursday, December 20, 2012 5:57 AM


Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Today @ 10:49 AM
Points: 513, Visits: 1,123
Hi,

I've a table that stores currency amount values and is used in some calculations (vat, discounts, ....).
FLOAT, REAL, MONEY and SMALLMONEY can have round issues due to their internal storage method in SQL Server being DECIMAL the only one that hasn't that issue.
What's the best mapping from each type to DECIMAL?
Should FLOAT and MONEY use the default DECIMAL precision (18) and REAL and SMALLMONEY 9 ?

Thanks,
Pedro




If you need to work better, try working less...
Post #1398932
Posted Thursday, December 20, 2012 7:27 AM


SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Monday, April 07, 2014 12:05 PM
Points: 183, Visits: 823
Technically FLOAT, REAL, MONEY and SMALLMONEY all have different ranges, but my standard is just to convert all numeric values of different types, especially values with currency, to Numeric(17,2), and for percents I use Numeric(17,6). Doing this keeps all numeric values standard across the database.

Also Numeric and Decimal are equivalents, I just have always used Numeric, but you can use Decimal just the same.

Hope this helps ..

Sam
Post #1398967
Posted Thursday, December 20, 2012 8:22 AM
Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Yesterday @ 7:01 PM
Points: 3,081, Visits: 11,229
samalex (12/20/2012)
Technically FLOAT, REAL, MONEY and SMALLMONEY all have different ranges, but my standard is just to convert all numeric values of different types, especially values with currency, to Numeric(17,2), and for percents I use Numeric(17,6). Doing this keeps all numeric values standard across the database.

Also Numeric and Decimal are equivalents, I just have always used Numeric, but you can use Decimal just the same.

Hope this helps ..

Sam


You could use NUMERIC(19,2) and NUMERIC(19,6) as your standards, since they use the same amount of storage, 9 bytes, as NUMERIC(17,2) and NUMERIC(17,6).

Post #1399002
Posted Friday, December 21, 2012 6:33 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Thursday, April 10, 2014 10:17 AM
Points: 7,070, Visits: 12,522
Comparing FLOAT and REAL to DECIMAL and NUMERIC is not really fair. The former are approximate numeric data types and the latter are exact numeric data types. I am leaving MONEY and SMALLMONEY out of it because those should never be used. For example if you're currently using FLOAT and REAL to sum the precent-segments of a set where the percentage sum should equal 100.00 then if you switch to an exact data type you will start having issues, e.g. coming up with 99.76 or some non-trivial amount away from 100.00. Just throwing it out there, be sure you test to ensure you have chosen the right data type for the job.

__________________________________________________________________________________________________
There are no special teachers of virtue, because virtue is taught by the whole community. --Plato

Believe you can and you're halfway there. --Theodore Roosevelt

Everything Should Be Made as Simple as Possible, But Not Simpler --Albert Einstein

The significant problems we face cannot be solved at the same level of thinking we were at when we created them. --Albert Einstein

1 apple is not exactly 1/8 of 8 apples. Because there are no absolutely identical apples. --Giordy
Post #1399373
Posted Friday, December 21, 2012 8:12 AM


Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Today @ 10:49 AM
Points: 513, Visits: 1,123
opc.three (12/21/2012)
Comparing FLOAT and REAL to DECIMAL and NUMERIC is not really fair. The former are approximate numeric data types and the latter are exact numeric data types. I am leaving MONEY and SMALLMONEY out of it because those should never be used. For example if you're currently using FLOAT and REAL to sum the precent-segments of a set where the percentage sum should equal 100.00 then if you switch to an exact data type you will start having issues, e.g. coming up with 99.76 or some non-trivial amount away from 100.00. Just throwing it out there, be sure you test to ensure you have chosen the right data type for the job.


My main problem wanting to change to DECIMAL is that we deal with money values and store the value with out VAT, therefore it can be a long decimal value...
But cause we store it in float the invoice report, occasionally, gives different values for the total since each line is VALUE + VAT and the total is SUM(VALUE) + VAT and sometimes they are different due to the floating point issue, and also we had 2 reports with different values (cents but different...).

Thanks,
Pedro




If you need to work better, try working less...
Post #1399437
Posted Friday, December 21, 2012 9:06 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Thursday, April 10, 2014 10:17 AM
Points: 7,070, Visits: 12,522
For $ I would recommend an exact numeric data type with the scale you need to satisfy your accounting requirements.

__________________________________________________________________________________________________
There are no special teachers of virtue, because virtue is taught by the whole community. --Plato

Believe you can and you're halfway there. --Theodore Roosevelt

Everything Should Be Made as Simple as Possible, But Not Simpler --Albert Einstein

The significant problems we face cannot be solved at the same level of thinking we were at when we created them. --Albert Einstein

1 apple is not exactly 1/8 of 8 apples. Because there are no absolutely identical apples. --Giordy
Post #1399470
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse