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

What will this return? Expand / Collapse
Author
Message
Posted Saturday, May 22, 2010 10:18 PM
SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Yesterday @ 11:38 AM
Points: 1,809, Visits: 479
Comments posted to this topic are about the item What will this return?
Post #926485
Posted Monday, May 24, 2010 12:07 AM
SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Monday, June 30, 2014 2:51 AM
Points: 1,976, Visits: 369
had to solve this using ssms 2005... the query length was too much for me :)
Post #926623
Posted Monday, May 24, 2010 2:07 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 3:35 PM
Points: 5,930, Visits: 8,179
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

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Tuesday, August 27, 2013 4:28 AM
Points: 1,248, 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...


_____________________________________________________________________
"The difficult tasks we do immediately, the impossible takes a little longer"
Post #926666
Posted Monday, May 24, 2010 3:19 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Thursday, July 24, 2014 4:28 AM
Points: 1,100, Visits: 4,898
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!
Post #926682
Posted Monday, May 24, 2010 3:22 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Tuesday, July 22, 2014 2:49 AM
Points: 1,179, Visits: 783
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

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Tuesday, August 27, 2013 4:28 AM
Points: 1,248, 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!


_____________________________________________________________________
"The difficult tasks we do immediately, the impossible takes a little longer"
Post #926686
Posted Monday, May 24, 2010 5:30 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Tuesday, June 8, 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"...!

------------------------
~Niths~
Hard Work never Fails
Post #926739
Posted Monday, May 24, 2010 5:30 AM
SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Yesterday @ 11:38 AM
Points: 1,809, Visits: 479
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


SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Friday, July 25, 2014 6:26 AM
Points: 846, Visits: 850
Yeah, I was kinda wishing for a "tldr" option.

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

Permissions Expand / Collapse