SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Round up or down IV


Round up or down IV

Author
Message
Hugo Kornelis
Hugo Kornelis
SSCoach
SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)

Group: General Forum Members
Points: 19043 Visits: 12426
paul.knibbs (8/24/2010)
Hugo Kornelis (8/24/2010)
Super! Thanks for testing and reporting back here.
Obviously, this weird behaviour that appears to be exclusive to the ROUND() function has been fixed in SQL Server 2008.


Is it a problem with the ROUND() function, or something else? It seems odd that when you select the content of WeirdTable before dropping it that you get the single 1000.00 value returned on SQL 2005--you would have thought it wouldn't allow you to insert that value in a DECIMAL(5,2) column in the first place, regardless of where it came from!

I think it's the ROUND() function, as I have so far not been able to reproduce this behavour in any other way.

Returning 1000.00 when you SELECT from the table is just as subject to the client being used as getting a result from running "SELECT ROUND(789.87, -3);" directly. QA and osql.exe display the value; SSMS returns an error message.


Hugo Kornelis, SQL Server MVP
Visit my SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis
webrunner
webrunner
SSCrazy Eights
SSCrazy Eights (8.1K reputation)SSCrazy Eights (8.1K reputation)SSCrazy Eights (8.1K reputation)SSCrazy Eights (8.1K reputation)SSCrazy Eights (8.1K reputation)SSCrazy Eights (8.1K reputation)SSCrazy Eights (8.1K reputation)SSCrazy Eights (8.1K reputation)

Group: General Forum Members
Points: 8071 Visits: 4001
hrvoje.piasevoli (8/23/2010)
Hi great qod!
Here's a tip:
Move the decimal point to the left by the negative number and then do a regular ROUND(d, 0). Applied to this it ends looking like ROUND(0.1666666,0) and there you have it.

I wish I'd come up with it before I answered incorrectly Smile

Regards,

Hrvoje Piasevoli


Thanks for the tip. It would have kept me from going 0 for 4 on these rounding questions. Sigh.

- webrunner

-------------------
"I love spending twice as long and working twice as hard to get half as much done!" – Nobody ever.
Ref.: http://www.adminarsenal.com/admin-arsenal-blog/powershell-how-to-write-your-first-powershell-script

"Operator! Give me the number for 911!" - Homer Simpson

"A SQL query walks into a bar and sees two tables. He walks up to them and says 'Can I join you?'"
Ref.: http://tkyte.blogspot.com/2009/02/sql-joke.html
Michael Poppers
Michael Poppers
Ten Centuries
Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)

Group: General Forum Members
Points: 1127 Visits: 416
paul.knibbs (8/24/2010)
Didn't know ROUND() could do this, but once I looked it up in Books Online it seemed fairly straightforward--I learned something today, which is always nice!

Get ready for a future QotD involving the opt. 3rd param. to ROUND() (oops, I gave it away :-)).
Hugo Kornelis
Hugo Kornelis
SSCoach
SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)

Group: General Forum Members
Points: 19043 Visits: 12426
Michael Poppers (8/24/2010)
paul.knibbs (8/24/2010)
Didn't know ROUND() could do this, but once I looked it up in Books Online it seemed fairly straightforward--I learned something today, which is always nice!

Get ready for a future QotD involving the opt. 3rd param. to ROUND() (oops, I gave it away :-)).

Nope, I already did that last week! http://www.sqlservercentral.com/questions/T-SQL/70325/

The good news for Michael is that this was the last question I submitted about ROUND().

The bad news is that I still have two other questions scheduled for the next two tuesdays.:-P


Hugo Kornelis, SQL Server MVP
Visit my SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis
Michael Poppers
Michael Poppers
Ten Centuries
Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)

Group: General Forum Members
Points: 1127 Visits: 416
Hugo Kornelis (8/24/2010)
Nope, I already did that last week! http://www.sqlservercentral.com/questions/T-SQL/70325/

Sorry -- I was away from computers last week and missed it (but I guess I can now look at it :-)).

The good news for Michael is that this was the last question I submitted about ROUND().

Thanks for helping us learn more about it!

The bad news is that I still have two other questions scheduled for the next two tuesdays.:-P

Keep up the good work!
hrvoje.piasevoli
hrvoje.piasevoli
Mr or Mrs. 500
Mr or Mrs. 500 (573 reputation)Mr or Mrs. 500 (573 reputation)Mr or Mrs. 500 (573 reputation)Mr or Mrs. 500 (573 reputation)Mr or Mrs. 500 (573 reputation)Mr or Mrs. 500 (573 reputation)Mr or Mrs. 500 (573 reputation)Mr or Mrs. 500 (573 reputation)

Group: General Forum Members
Points: 573 Visits: 510
Hi all, played for a while with Hugo's example and ... look what I found w00t

First, I have tested and it errors on 2008, works on 2005.
So, the following applies to SQL Server 2008. Here is the script, note the results from selects in the end of the script:

USE [master]
GO

IF EXISTS (SELECT name FROM sys.databases WHERE name = N'Weird') BEGIN
ALTER DATABASE [Weird] SET SINGLE_USER WITH ROLLBACK IMMEDIATE
DROP DATABASE [Weird]
END
GO

CREATE DATABASE [Weird]
GO

-- set compatibility level
ALTER DATABASE [Weird] SET COMPATIBILITY_LEVEL = 90
GO

ALTER DATABASE [Weird] SET RECOVERY SIMPLE
GO

USE weird
go

SELECT ROUND(789.98,-3) AS WeirdCol
INTO WeirdTable;

-- 1 ok
SELECT COUNT(*) FROM WeirdTable
-- 2 doesn't work
SELECT WeirdCol FROM WeirdTable
--3 explicit cast works !!!
SELECT CAST(WeirdCol as decimal(6,2)) FROM WeirdTable



Now you have to admit that this is realy weirdHehe

PS: If I had a blog this would probably be worth blogging about. Hugo?

Regards,

Hrvoje Piasevoli

Hrvoje Piasevoli
Hugo Kornelis
Hugo Kornelis
SSCoach
SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)

Group: General Forum Members
Points: 19043 Visits: 12426
hrvoje.piasevoli (8/24/2010)
Hi all, played for a while with Hugo's example and ... look what I found w00t

Thanks for confirming that this is indeed apparently fixed in SQL 2008.

-- 2 doesn't work
SELECT WeirdCol FROM WeirdTable


I'm willing to bet that this will work when run from Query Analyzer or osql.exe

PS: If I had a blog this would probably be worth blogging about. Hugo?

You can always start a blog! ;-)


Hugo Kornelis, SQL Server MVP
Visit my SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis
SQLRNNR
SQLRNNR
SSC Guru
SSC Guru (68K reputation)SSC Guru (68K reputation)SSC Guru (68K reputation)SSC Guru (68K reputation)SSC Guru (68K reputation)SSC Guru (68K reputation)SSC Guru (68K reputation)SSC Guru (68K reputation)

Group: General Forum Members
Points: 68087 Visits: 18570
Thanks for another great question.



Jason AKA CirqueDeSQLeil
I have given a name to my pain...
MCM SQL Server, MVP


SQL RNNR

Posting Performance Based Questions - Gail Shaw

ziangij
ziangij
Hall of Fame
Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)

Group: General Forum Members
Points: 3615 Visits: 377
Hugo Kornelis (8/24/2010)
paul.knibbs (8/24/2010)
Nope, just tested it on a SQL 2008 Express install and it came up with an arithmetic overflow as expected.

Super! Thanks for testing and reporting back here.
Obviously, this weird behaviour that appears to be exclusive to the ROUND() function has been fixed in SQL Server 2008.


i m still getting the error in sql server 2008... Unsure

compatibility level - 100...
Microsoft SQL Server 2008 (SP1) - 10.0.2723.0 (X64) Enterprise Edition (64-bit)
UMG Developer
UMG Developer
SSCarpal Tunnel
SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)

Group: General Forum Members
Points: 4104 Visits: 2204
Thanks for the question! (Actually for the series of questions, I like the idea of going through lots of different uses of the same function.)
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search