May 22, 2010 at 10:18 pm
Comments posted to this topic are about the item What will this return?
May 24, 2010 at 12:07 am
had to solve this using ssms 2005... 🙁 the query length was too much for me 🙂
May 24, 2010 at 2:07 am
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.
May 24, 2010 at 2:42 am
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:
_____________________________________________________________________
[font="Comic Sans MS"]"The difficult tasks we do immediately, the impossible takes a little longer"[/font]
May 24, 2010 at 3:19 am
I managed to work out what all the date functions were doing, but was caught out by the select @hourDiff = @min-2 / 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! 😎
May 24, 2010 at 3:22 am
it took 10 mins to go through the date functions to finally arrive at the result of 90 mins and then it is simple.
May 24, 2010 at 3:31 am
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:
_____________________________________________________________________
[font="Comic Sans MS"]"The difficult tasks we do immediately, the impossible takes a little longer"[/font]
May 24, 2010 at 5:30 am
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 🙂
May 24, 2010 at 5:30 am
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
May 24, 2010 at 5:34 am
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
May 24, 2010 at 5:47 am
Hugo,
To do what you suggest would cause this Question to be too easy. Perhaps you are fortunate enough to NOT have inherited some poor code from previous employees. I have some sections of code that were not well written by previous employees. Those employees are long gone, so it is up to me to work through some overly complicated code and figure out where the bug could be. At first glance one might think it is in the complicated code, but in the end it was just a simple error in division. So this question should put the reader in a little bit of the same situation I was in when I thought up this question.
Ben
May 24, 2010 at 6:54 am
How terrible this question was...
I just could not bear trying to work that one out.
Far too long and unreadable.
If anything we learned that "simpler is better".
Unfortunately, we all knew it already!
May 24, 2010 at 7:32 am
bkubicek (5/24/2010)
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
I have to say I was a little disappointed not to find "pink slip" as one of the options. That was the thought I kept having as I was trying to unravel the overly complicated date math. I am glad to hear they are previous employees. 😉
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
May 24, 2010 at 8:08 am
bkubicek (5/24/2010)
Hugo,To do what you suggest would cause this Question to be too easy. Perhaps you are fortunate enough to NOT have inherited some poor code from previous employees. I have some sections of code that were not well written by previous employees. Those employees are long gone, so it is up to me to work through some overly complicated code and figure out where the bug could be. At first glance one might think it is in the complicated code, but in the end it was just a simple error in division. So this question should put the reader in a little bit of the same situation I was in when I thought up this question.
Ben
You are right, Ben. The complicated datetime expressions shove the point of the question - integer division. You are right, it's reality.
I have some inherited code (C#), not refactored yet (not so important), I am using as an example "Not this way".
May 24, 2010 at 8:33 am
Niths (5/24/2010)
I executed this query in SQL Client 2008 but i did not receive any output. Instead it just said "Commands executed successfully"...!:(
The QotD was "What result will @hourdiff hold?" rather than (as you implied) "What is the output?" 😉
Viewing 15 posts - 1 through 15 (of 57 total)
You must be logged in to reply to this topic. Login to reply