Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I'm on SQL Server 2005, and I am trying to figure out a rounding issue with smallmoney data types.

For example, the value 1.2849, when rounded to 2 decimal places, produces 1.28, but shouldn't it produce 1.29? e.g. 9 rounds up for 1.285, 5 rounds up for 1.29.

Here is the example:

SELECT
    'CAST(1.2849 AS SMALLMONEY)' OPERATION, CAST(1.284948 AS SMALLMONEY) RESULT
UNION
SELECT
    'ROUND(CAST(1.2849 AS SMALLMONEY), 2)', ROUND(CAST(1.284948 AS SMALLMONEY), 2)
UNION
SELECT
    'ROUND(ROUND(CAST(1.2849 AS SMALLMONEY), 3), 2)', ROUND(ROUND(CAST(1.2849 AS SMALLMONEY), 3), 2)
share|improve this question

1 Answer 1

up vote 4 down vote accepted

I am fairly certain that it just looks at the third decimal digit... and that's 4 so it rounds down to 1.28.

Clarify - it looks to the digit one greater than what you are rounding to... so it looks to the third because you are rounding to the second.

share|improve this answer
2  
I concur. The laws of math make it clear that rounding in this instance should go down, not up. –  jcolebrand Mar 22 '12 at 16:00
    
Got it... so you are saying that it is behaving in a mathematically correct way, right? –  robarwebservices Mar 22 '12 at 16:01
    
Yep. It's mathematically correct! –  JHFB Mar 22 '12 at 16:05
    
@cbwdev: Yes, it's closer to 1.28 than to 1.29 –  ypercube Mar 22 '12 at 16:06
1  
@JHFB - sorry for the delay. Accepted your answer. Thanks again for the time. –  robarwebservices May 14 '12 at 13:02

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.