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 ««123»»

Round up or down I Expand / Collapse
Author
Message
Posted Tuesday, August 3, 2010 6:40 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Today @ 1:09 PM
Points: 2,074, Visits: 2,068
Nice question. Had to jump start the grey matter first thing in the morning to get this one.

I especially appreciated all the neatly terminated statements. ;)
Post #962745
Posted Tuesday, August 3, 2010 6:59 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Tuesday, September 16, 2014 7:35 AM
Points: 2,817, Visits: 2,563
I learned this the hard way. It happened to me twice before I finally filed it away in the long term memory. Nice question, thanks.
Post #962759
Posted Tuesday, August 3, 2010 7:12 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Tuesday, October 12, 2010 4:24 AM
Points: 41, Visits: 102
oops..i did wrong..

Learned new point today and is,

Don't think how it is small..think is it correct or not?

Thanks..


Post #962768
Posted Tuesday, August 3, 2010 7:36 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Thursday, January 24, 2013 9:59 PM
Points: 1,354, Visits: 1,299
ronmoses (8/3/2010)
Yep, I've been bitten by that one more than once. Until you learn that int/int "gotcha" it can be nearly impossible to troubleshoot a script that is experiencing it. Good question.


Unfortunately, this gets me all the time. I know about the issue, I know the workarounds but it still fails to sink in until after I run the query and don't get the expected results. I failed again when answering the QOTD.
Post #962797
Posted Tuesday, August 3, 2010 8:00 AM


Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Monday, July 14, 2014 10:08 AM
Points: 598, Visits: 3,816
I think that this "feature" should be changed. Just because two numbers are integers, why assume the answer should also be an integer?

______________________________________________________________________
The greatest enemy of knowledge is not ignorance, it is the illusion of knowledge. - Stephen Hawking
Post #962826
Posted Tuesday, August 3, 2010 8:21 AM
SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Wednesday, September 17, 2014 9:21 AM
Points: 1,676, Visits: 1,757
mtillman-921105 (8/3/2010)
I think that this "feature" should be changed. Just because two numbers are integers, why assume the answer should also be an integer?

I would like to disagree with this. The "feature" happens to be default, consistent behaviour in many programming languages. Try this is C#:

int i = 5, j = 3;
Console.WriteLine(i / j);

The above happily prints 1 to the console window. Why would anyone think that T-SQL should behave differently?

Very good question Hugo, thank you. It does not happen too often to be able to answer your QotD right off the bat without thinking much.

Oleg
Post #962855
Posted Tuesday, August 3, 2010 10:54 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
Oleg Netchaev (8/3/2010)
mtillman-921105 (8/3/2010)
I think that this "feature" should be changed. Just because two numbers are integers, why assume the answer should also be an integer?

I would like to disagree with this. The "feature" happens to be default, consistent behaviour in many programming languages. Try this is C#:

int i = 5, j = 3;
Console.WriteLine(i / j);

The above happily prints 1 to the console window. Why would anyone think that T-SQL should behave differently?

<<snip>>

Oleg

The difference, Oleg, is that the 1st argument to ROUND() is a NUMERIC. I would nevertheless not want T-SQL's behavior to change .
Post #962982
Posted Tuesday, August 3, 2010 10:58 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Friday, September 5, 2014 2:00 PM
Points: 2,160, Visits: 2,191
Nice question thanks! (It is nice to know that the issues I have run into in the past have taught me some things, and that I still remember them.)
Post #962987
Posted Tuesday, August 3, 2010 11:17 AM
SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Wednesday, September 17, 2014 9:21 AM
Points: 1,676, Visits: 1,757
Michael Poppers (8/3/2010)

The difference, Oleg, is that the 1st argument to ROUND() is a NUMERIC. I would nevertheless not want T-SQL's behavior to change .

This is true, but who said that int is not numeric? Well, I will actually switch from numeric wordy to decimal at this point. I understand that they are synonyms and numeric is ANSI standard, but it just smells like Oracle to me, so I prefer decimal instead. Int can be considered as decimal(10, 0). From BOL:

For example, int data type can hold 10 digits, is stored in 4 bytes, and does not accept decimal points. The int data type has a precision of 10, a length of 4, and a scale of 0.


So, both statements below:

select round(5, 1);
select round(cast(5 as decimal(10,0)), 1);

are pretty similar, both print 5 in the results window.

Oleg

Post #962997
Posted Tuesday, August 3, 2010 11:27 AM


Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Monday, July 14, 2014 10:08 AM
Points: 598, Visits: 3,816
Oleg Netchaev (8/3/2010)
mtillman-921105 (8/3/2010)
I think that this "feature" should be changed. Just because two numbers are integers, why assume the answer should also be an integer?

I would like to disagree with this. The "feature" happens to be default, consistent behaviour in many programming languages. Try this is C#:

int i = 5, j = 3;
Console.WriteLine(i / j);

The above happily prints 1 to the console window. Why would anyone think that T-SQL should behave differently?

Very good question Hugo, thank you. It does not happen too often to be able to answer your QotD right off the bat without thinking much.

Oleg


Oleg, first, my standard is my calculator and if I put in 5/3, it shows 1.66666666.

Second, logically, 5 / 3 <> 1. It is actually closer to 2. So SQL is returning a false statement.

Third, us humans don't think this way typically I don't believe (thus, the good main question here). Since SQL is our tool, we should dictate how it works rather than vice-versa.

However, I can accept your line of reasoning, but I still don't agree.


______________________________________________________________________
The greatest enemy of knowledge is not ignorance, it is the illusion of knowledge. - Stephen Hawking
Post #963009
« Prev Topic | Next Topic »

Add to briefcase ««123»»

Permissions Expand / Collapse