# Round

• Didn`t know it will result in this, had 2 test it 2 make sure, thanx 4 the good question.

• For fun

😎

`SELECT ROUND(0.999,-1);`

SELECT ROUND(\$0.999,1)

And

SELECT ROUND(0.999,\$3)

?

--------------------------------------
;-)“Everything has beauty, but not everyone sees it.” ― Confucius

• An easy question only because I have had the same question in an interview...

• Nice one, thanks.

Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP

• Looks a bit strange to me.

SELECT ROUND(1.999,2) returns 2.000

SELECT ROUND(10.999,2) returns 10.000

but

SELECT ROUND(0.999,2) won't work

• Arno Kwetters (9/14/2015)

Looks a bit strange to me.

SELECT ROUND(1.999,2) returns 2.000

SELECT ROUND(10.999,2) returns 10.000

but

SELECT ROUND(0.999,2) won't work

Probably because with 0.999 SQL Server doesn't care what comes before the decimal point since it's a zero.

Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP

• That can be the explanation, but it is still strange.

A ROUND is a ROUND function

• select round(cast(0.999 as float),2) does the job. 🙂

• A good question, but the explanation isn't very helpful

• This is a serious fault in a basic function in SQL Server; my conclusion is based on the behaviour on SQL Server of:

select ROUND(1.999, 2);

2.000

And Oracle 10g:

SELECT ROUND(0.999,2) from dual;

1

And DB2

select ROUND(0.999, 2) from SYSIBM.SYSDUMMY1;

1.000

Only SQL Server over many versions and many years fails to return a correct result and instead fails.

This shouldn't be a trivia question but rather a serious question as to whether if SQL Server cannot do standard maths functions correctly it should be considered as a candidate tool for implementation by any organisation.

Worse - this has been know about for years, and there are no possible detrimental impacts for correcting the fault, but still Microsoft has done nothing about it.

It is failures like these that damage the reputation of companies such as the one I work for; we implement the same software on multiple database platforms and have to rely on standard sql functions to work consistently. We do test properly on the multiple platforms, but you can't test everything and why would our testing teams be looking for edge cases such as this one?

• amen, Matthew

• This is one of those things to file away in the memory banks. I think it is a good lesson on defining your columns and variables with the right data type, including scale and precision, to cover your data. Thanks very much for a good question.

• I agree that the behavior of SQL Server is unexpected as it interprets the 0.999 as a decimal(3,3) which won't allow integers.

However, if you're using this code, you deserve the error. No one should be rounding a literal value. Just type the rounded value. If it isn't the literal, there;s a data type involved and you should be able to predict the error.

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2

Viewing 15 posts - 1 through 15 (of 26 total)