What will this return?

Click here to monitor SSC

<a href="http://g.adspeed.net/ad.php?do=clk&zid=15220&wd=468&ht=60&pair=as" target="_blank"><img style="border:0px;" src="http://g.adspeed.net/ad.php?do=img&zid=15220&wd=468&ht=60&pair=as" alt="i" width="468" height="60"/></a>

SQLServerCentral is supported by Red Gate Software Ltd.

Log in :: Register :: Not logged in

<a href="http://g.adspeed.net/ad.php?do=clk&zid=15491&wd=120&ht=300&pair=as" target="_top"><img style="border:0px;" src="http://g.adspeed.net/ad.php?do=img&zid=15491&wd=120&ht=300&pair=as" alt="i" width="120" height="300"/></a>

Recent Posts

Popular Topics

Home

Home » Article Discussions » Article Discussions by Author » Discuss content posted by Ben Kubicek » What will this return?

Add to briefcase

57 posts, Page 1 of 6

1 2 3 4 5 »»»

What will this return?

Expand / Collapse

Author

Message

Posted Saturday, May 22, 2010 10:18 PM

UDP Broadcaster

Group: General Forum Members
Last Login: Friday, February 01, 2013 8:13 AM
Points: 1,491, Visits: 388

Comments posted to this topic are about the item What will this return?

Post #926485

Posted Monday, May 24, 2010 12:07 AM

SSCommitted

Group: General Forum Members
Last Login: Friday, May 17, 2013 2:53 AM
Points: 1,530, Visits: 359

had to solve this using ssms 2005... Sad

Sad

the query length was too much for me :)

Post #926623

Posted Monday, May 24, 2010 2:07 AM

SSCertifiable

Group: General Forum Members
Last Login: Today @ 3:49 PM
Points: 5,244, Visits: 7,063

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

Post #926653

Posted Monday, May 24, 2010 2:42 AM

Ten Centuries

Group: General Forum Members
Last Login: Yesterday @ 3:25 AM
Points: 1,165, Visits: 731

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

Whistling

_____________________________________________________________________
"The difficult tasks we do immediately, the impossible takes a little longer"

Post #926666

Posted Monday, May 24, 2010 3:19 AM

Ten Centuries

Group: General Forum Members
Last Login: Today @ 7:40 PM
Points: 1,060, Visits: 4,184

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

Cool

Post #926682

sharath.chalamgari

Posted Monday, May 24, 2010 3:22 AM

Ten Centuries

Group: General Forum Members
Last Login: Monday, September 17, 2012 7:30 AM
Points: 1,038, Visits: 679

it took 10 mins to go through the date functions to finally arrive at the result of 90 mins and then it is simple.

Post #926684

Posted Monday, May 24, 2010 3:31 AM

Ten Centuries

Group: General Forum Members
Last Login: Yesterday @ 3:25 AM
Points: 1,165, Visits: 731

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

Blush

_____________________________________________________________________
"The difficult tasks we do immediately, the impossible takes a little longer"

Post #926686

Posted Monday, May 24, 2010 5:30 AM

SSC Rookie

Group: General Forum Members
Last Login: Tuesday, June 08, 2010 7:47 AM
Points: 26, 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

Sad

------------------------
~Niths~
Hard Work never Fails Smile

Smile

Post #926739

Posted Monday, May 24, 2010 5:30 AM

UDP Broadcaster

Group: General Forum Members
Last Login: Friday, February 01, 2013 8:13 AM
Points: 1,491, Visits: 388

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

Post #926740

Posted Monday, May 24, 2010 5:34 AM

SSChasing Mays

Group: General Forum Members
Last Login: Yesterday @ 10:36 AM
Points: 651, Visits: 688

Yeah, I was kinda wishing for a "tldr" option. Tongue

Tongue

-----
a haiku...

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

Post #926743

« Prev Topic | Next Topic »

Add to briefcase

57 posts, Page 1 of 6

1 2 3 4 5 »»»

Permissions

Expand / Collapse