September 13, 2015 at 12:15 am
Didn`t know it will result in this, had 2 test it 2 make sure, thanx 4 the good question.
Thanks & Best Regards,
Hany Helmy
SQL Server Database Consultant
September 13, 2015 at 1:46 am
For fun
😎
SELECT ROUND(0.999,-1);
September 13, 2015 at 5:36 am
How About
SELECT ROUND($0.999,1)
And
SELECT ROUND(0.999,$3)
?
--------------------------------------
;-)“Everything has beauty, but not everyone sees it.” ― Confucius
September 13, 2015 at 11:45 am
An easy question only because I have had the same question in an interview...
September 14, 2015 at 12:02 am
Nice one, thanks.
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
September 14, 2015 at 12:49 am
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
September 14, 2015 at 12:52 am
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
September 14, 2015 at 12:56 am
That can be the explanation, but it is still strange.
A ROUND is a ROUND function
September 14, 2015 at 1:43 am
select round(cast(0.999 as float),2) does the job. 🙂
September 14, 2015 at 1:59 am
A good question, but the explanation isn't very helpful
September 14, 2015 at 2:44 am
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?
September 14, 2015 at 2:51 am
amen, Matthew
September 14, 2015 at 5:14 am
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.
September 14, 2015 at 6:33 am
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.
Viewing 15 posts - 1 through 15 (of 26 total)
You must be logged in to reply to this topic. Login to reply