Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase «««1234»»

Round up or down IV Expand / Collapse
Author
Message
Posted Tuesday, August 24, 2010 7:25 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 10:24 AM
Points: 5,977, Visits: 8,239
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
Post #974099
Posted Tuesday, August 24, 2010 8:08 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Wednesday, August 27, 2014 8:24 AM
Points: 2,351, Visits: 2,700
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 :)

Regards,

Hrvoje Piasevoli


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

- webrunner


-------------------
"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
Post #974132
Posted Tuesday, August 24, 2010 8:40 AM
SSChasing Mays

SSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing Mays

Group: General Forum Members
Last Login: Monday, July 7, 2014 8:58 AM
Points: 654, Visits: 400
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 ).
Post #974174
Posted Tuesday, August 24, 2010 8:50 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 10:24 AM
Points: 5,977, Visits: 8,239
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.



Hugo Kornelis, SQL Server MVP
Visit my SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis
Post #974189
Posted Tuesday, August 24, 2010 9:00 AM
SSChasing Mays

SSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing Mays

Group: General Forum Members
Last Login: Monday, July 7, 2014 8:58 AM
Points: 654, Visits: 400
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.

Keep up the good work!
Post #974201
Posted Tuesday, August 24, 2010 9:21 AM


Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Wednesday, July 30, 2014 11:33 PM
Points: 361, Visits: 510
Hi all, played for a while with Hugo's example and ... look what I found

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 weird

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

Regards,

Hrvoje Piasevoli


Hrvoje Piasevoli
Post #974219
Posted Tuesday, August 24, 2010 11:25 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 10:24 AM
Points: 5,977, Visits: 8,239
hrvoje.piasevoli (8/24/2010)
Hi all, played for a while with Hugo's example and ... look what I found

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
Post #974323
Posted Tuesday, August 24, 2010 1:01 PM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Friday, August 29, 2014 1:51 PM
Points: 21,644, Visits: 15,317
Thanks for another great question.



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


SQL RNNR

Posting Performance Based Questions - Gail Shaw
Posting Data Etiquette - Jeff Moden
Hidden RBAR - Jeff Moden
VLFs and the Tran Log - Kimberly Tripp
Post #974382
Posted Tuesday, August 24, 2010 10:53 PM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Monday, June 30, 2014 2:51 AM
Points: 2,010, Visits: 369
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...

compatibility level - 100...
Microsoft SQL Server 2008 (SP1) - 10.0.2723.0 (X64) Enterprise Edition (64-bit)

Post #974628
Posted Wednesday, August 25, 2010 6:01 PM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Monday, June 16, 2014 9:38 AM
Points: 2,163, Visits: 2,189
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.)
Post #975282
« Prev Topic | Next Topic »

Add to briefcase «««1234»»

Permissions Expand / Collapse