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


What will this return?


What will this return?

Author
Message
bkubicek
bkubicek
SSCrazy
SSCrazy (3K reputation)SSCrazy (3K reputation)SSCrazy (3K reputation)SSCrazy (3K reputation)SSCrazy (3K reputation)SSCrazy (3K reputation)SSCrazy (3K reputation)SSCrazy (3K reputation)

Group: General Forum Members
Points: 2976 Visits: 811
Comments posted to this topic are about the item What will this return?
ziangij
ziangij
Hall of Fame
Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)

Group: General Forum Members
Points: 3097 Visits: 376
had to solve this using ssms 2005... Sad the query length was too much for me Smile
Hugo Kornelis
Hugo Kornelis
SSChampion
SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)

Group: General Forum Members
Points: 10777 Visits: 11966
I'll admit this right away - I cheated; I used copy and paste to have SSMS work out the answer for me.

I don't feel bad about it either. I know about integer division and I understand all about dateadd and datediff (I even added some tricks to Tibor Karaszi's ultimate guide to the datetime datatypes), so I think I deserve these points. But feel free to disagree.

The reason I cheated is that the expressions used to set the two datetime variables are way too complex to understand. Especially with their (lack of) formatting. Having to scroll horizontally to see the end of an expression is never a good sign. Same goes for having to count parenthese to find out what belongs together and what doesn't. If one of my staff (which in reality I don't have) turned in this code, I'd send him/her back.

From the explanation, I understand that the focus is not on the lengthy expressions but on the division that follows. So why not, instead of doing al that hard work to set the datetimes to "today" at a fixed time, use a fixed date as well:
SET @startDt = '2010-05-01T10:30:00';
SET @endDt = '2010-05-01T12:00:00';


Then I would have been able to answer the question without cheating, and felt even better about my point.


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

Group: General Forum Members
Points: 1414 Visits: 779
I have to agree with Hugo here, if the question is overly complicated involving long "unreadable" calculations, then it's going to send most people I would imagine down the path to the dark side.

Especially, as pointed out, if it is unnecessary for the lesson.

ps. Sorry, I cheated too... Whistling

_____________________________________________________________________
"The difficult tasks we do immediately, the impossible takes a little longer"
jts2013
jts2013
Ten Centuries
Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)

Group: General Forum Members
Points: 1254 Visits: 5009
I managed to work out what all the date functions were doing, but was caught out by the select @hourDiff = @min / 60. Since @hourDiff was defined as numeric(10,4), I thought it would come out OK as 1.5.

So I definitely learned something, which is what it's all about! Cool
sharath.chalamgari
sharath.chalamgari
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: 1464 Visits: 798
it took 10 mins to go through the date functions to finally arrive at the result of 90 mins and then it is simple.
DugyC
DugyC
Ten Centuries
Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)

Group: General Forum Members
Points: 1414 Visits: 779
You know what... you guys are right.

It's just took me less than a minute to work out he was just setting the hour to 10:30 and 12:00 respectively, and then of course the result of the calculation will be an integer hence 1.0000 and not 1.5000

Still learnt a lesson though... don't be so lazy in future! Blush

_____________________________________________________________________
"The difficult tasks we do immediately, the impossible takes a little longer"
Niths
Niths
SSC Rookie
SSC Rookie (38 reputation)SSC Rookie (38 reputation)SSC Rookie (38 reputation)SSC Rookie (38 reputation)SSC Rookie (38 reputation)SSC Rookie (38 reputation)SSC Rookie (38 reputation)SSC Rookie (38 reputation)

Group: General Forum Members
Points: 38 Visits: 42
I executed this query in SQL Client 2008 but i did not receive any output. Instead it just said "Commands executed successfully"...!Sad

------------------------
~Niths~
Hard Work never Fails :-)
bkubicek
bkubicek
SSCrazy
SSCrazy (3K reputation)SSCrazy (3K reputation)SSCrazy (3K reputation)SSCrazy (3K reputation)SSCrazy (3K reputation)SSCrazy (3K reputation)SSCrazy (3K reputation)SSCrazy (3K reputation)

Group: General Forum Members
Points: 2976 Visits: 811
Sorry for the misdirection. I thought it was what made this question fun. I found a problem just like this in some code I inherited from some previous employees. I spent two days digging through some complicated code just to end up at a division problem. That is why I liked this one. It makes you feel you are done once you make it through the date stuff, but then you didn't notice the integer division.

Anyway, I am glad you all spent the time to try and figure it out. Perhaps the next question I think up won't have quite as much misdirection.

Ben
ronmoses
ronmoses
Ten Centuries
Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)

Group: General Forum Members
Points: 1219 Visits: 996
Yeah, I was kinda wishing for a "tldr" option. :-P

-----
a haiku...

NULL is not zero
NULL is not an empty string
NULL is the unknown

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