ROUNDING OR FORMATTING

[EPNICO]

Hello Everyone:

Here is my problem I have a field that is numeric and the contents are 86.68000000000000007 field name is emhoursperpay. In order to find hours worked per week my formula is the following

round(((emhoursperpay * 24.00/52.00),2) as hoursworkedperweek
The formula above works up to a point in some cames I still get numbers like the one below
40.009999999999998 when I'm expecting 40.00? Any ideas

Thanks.

 

[karluk]

You don't have the same number of left and right parentheses in your expression. This may be a harmless typo, but perhaps there is something wrong with the formula. What is it exactly?

 

[EPNICO]

I missed one Paren. you are right I just copied it wrong from the sql query this is it round(((emhoursperpay * 24.00)/52.00),2) as hoursworkedperweek

The formula is a payroll formula to calculate hours worked per week given hours per pay.

My main goal is to round to two decimal digits. Maybe there is a better way to do this MS SQL 2000.

Thanks.

 

[rac2]

Well the word "numeric" is interesting. The explanation may depend on the database you are using, not on ANSI SQL, so you might get a better answer if you post this question in the forum for your database.

In MS SQL Server "numeric" is a datatype of a column. When you define a column as a NUMERIC datatype, you specify the precision and scale of the values. NUMERIC values are stored exactly. For example
distance_to_starbucks NUMERIC(4,3) could be any value between -9.999 and 9.999 with at most three decimal places. It could never be a value of 1.1020000000000001.

In MS SQL Server there is another datatype FLOAT which is a whole different animal. You might store the value 1.102 and find it to be equal to 1.1020000000000001 when you retrieve it. Values for this datatype are stored approximately, a specific number of binary digits are used to represent the value, and 1.102 cannot be stored exactly using 15 binary digits, the default for MS SQL Server FLOAT datatype.

In MS SQL Server the ROUND function does indeed return a value rounded off to the specified number of decimal places. So ROUND(distance_to_starbucks, 2) will return a value of 1.100 if the datatype is NUMERIC(4.3); and it will return a value of 1.1000000000000001 if the datatype is FLOAT.

The fine print in the documentation states that the datatype of the value returned by ROUND will be the same as that of the value supplied as an argument.

So this might explain what you are finding.

See Books Online especially Numeric Data and ROUND. Use query analyzer to see these results.

 

[EPNICO]

Thanks for your reply, in looking at the specs for this field I found out that it is monetary field? What is your opinion on this type.

Thanks for your ideas