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


Round up or down I


Round up or down I

Author
Message
Dave62
Dave62
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: 3551 Visits: 2831
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. Wink
Daniel Bowlin
Daniel Bowlin
SSCertifiable
SSCertifiable (7.7K reputation)SSCertifiable (7.7K reputation)SSCertifiable (7.7K reputation)SSCertifiable (7.7K reputation)SSCertifiable (7.7K reputation)SSCertifiable (7.7K reputation)SSCertifiable (7.7K reputation)SSCertifiable (7.7K reputation)

Group: General Forum Members
Points: 7674 Visits: 2629
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.
Ujar
Ujar
SSC-Enthusiastic
SSC-Enthusiastic (191 reputation)SSC-Enthusiastic (191 reputation)SSC-Enthusiastic (191 reputation)SSC-Enthusiastic (191 reputation)SSC-Enthusiastic (191 reputation)SSC-Enthusiastic (191 reputation)SSC-Enthusiastic (191 reputation)SSC-Enthusiastic (191 reputation)

Group: General Forum Members
Points: 191 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..
cengland0
cengland0
SSCrazy
SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)

Group: General Forum Members
Points: 2214 Visits: 1300
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.
mtillman-921105
mtillman-921105
UDP Broadcaster
UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)

Group: General Forum Members
Points: 1466 Visits: 3852
I think that this "feature" should be changed. Just because two numbers are integers, why assume the answer should also be an integer? Angry

______________________________________________________________________
The greatest enemy of knowledge is not ignorance, it is the illusion of knowledge. - Stephen Hawking
Oleg Netchaev
Oleg Netchaev
SSCrazy
SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)

Group: General Forum Members
Points: 2169 Visits: 1817
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? Angry

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
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: 1105 Visits: 416
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? Angry

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 :-).
UMG Developer
UMG Developer
Hall of Fame
Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)

Group: General Forum Members
Points: 3928 Visits: 2204
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.)
Oleg Netchaev
Oleg Netchaev
SSCrazy
SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)

Group: General Forum Members
Points: 2169 Visits: 1817
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
mtillman-921105
mtillman-921105
UDP Broadcaster
UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)

Group: General Forum Members
Points: 1466 Visits: 3852
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? Angry

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
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