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 Monday, May 24, 2010 5:47 AM
SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Thursday, July 10, 2014 1:08 PM
Points: 1,807, Visits: 477
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
Post #926748
Posted Monday, May 24, 2010 6:54 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Monday, July 7, 2014 1:29 AM
Points: 1,189, Visits: 787
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!
Post #926792
Posted Monday, May 24, 2010 7:32 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 3:28 PM
Points: 13,103, Visits: 11,933
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 Moden's splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
Post #926825
Posted Monday, May 24, 2010 8:08 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Tuesday, July 1, 2014 6:20 AM
Points: 1,339, Visits: 1,312
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".




See, understand, learn, try, use efficient
© Dr.Plch
Post #926856
Posted Monday, May 24, 2010 8:33 AM
SSChasing Mays

SSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing Mays

Group: General Forum Members
Last Login: Monday, July 7, 2014 8:58 AM
Points: 643, Visits: 400
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?"
Post #926879
Posted Monday, May 24, 2010 8:54 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Yesterday @ 3:58 PM
Points: 1,297, Visits: 1,657
Hugo Kornelis (5/24/2010)

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.


Sorry, Hugo, but I have to disagree with you here. I was able to parse the datetime expressions without much trouble. More to the point, a more realistic scenario is that one of your staff writes this code, and you find out about it after it's either gotten to production or is ready to go there. At that point, you can fire this staff member (I don't argue that the code is hard to read) but you still have to deal with the code.

There was nothing stopping you from pasting into SSMS (or notepad or whatever) to reformat the code so you could parse it without running it. And if that's all you did, that's certainly not cheating.

Personally, I like this kind of question because it tests more than "do you know how SQL server assigns result datatypes?"
Post #926895
Posted Monday, May 24, 2010 9:12 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Yesterday @ 9:08 AM
Points: 2,330, Visits: 2,664
Got it right through pure guessing. Perhaps that means, as others have noted, that it was not the best question as far as teaching/learning, due to the complexity of the functions.

If someone can show me how I could have worked through this question on paper, as I have done with simpler queries in the past, I think that would be of value to my SQL education.

- webrunner


-------------------
"Operator! Give me the number for 911!" - Homer Simpson

"A SQL query walks into a bar and sees two tables. He walks up to them and says 'Can I join you?'"
Ref.: http://tkyte.blogspot.com/2009/02/sql-joke.html
Post #926908
Posted Monday, May 24, 2010 9:21 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Friday, July 11, 2014 12:42 PM
Points: 1,380, Visits: 2,682
Hugo Kornelis (5/24/2010)
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.


If it were up to me I'd give Hugo 2 points for his explanation. I too thought the formatting was horrific. Over the years I've done my fair share of code clean up from previous contractors or former employees. When I've had a team I definately would of sent that code back, perhaps even would of had a one on one with them going over why it was unacceptable.

I don't think the QOD should be the place to practice code clean up.


---------------------------------------------------------------------
Use Full Links:
KB Article from Microsoft on how to ask a question on a Forum
Post #926912
Posted Monday, May 24, 2010 9:28 AM
SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Thursday, July 10, 2014 1:08 PM
Points: 1,807, Visits: 477
Webrunner,

select @startDt = getdate()
select @startDt =
dateadd(millisecond,0-datepart(millisecond,@startDt),
dateadd(second,0-datepart(second,@startDt),
dateadd(minute, 30-datepart(minute,@startDt),
dateadd(hour,10-datepart(hour,@startDt),
@startDt))))

So the idea is that you can start with any date time and set it to a specific time.
If you want the time to be 10:30, then you need to figure out what the current hour and minutes are and set them to 10:30. A way this can be done is to take the hour you want and subtrack it from the current hour. So if the current hour is 8, then 10-8 is 2, you add 2 to 8 and get 10, which is what you want.
The second and millisecond part is just to remove the seconds and milliseconds from the current datetime.
It also causes more confusion unless you figure out what it is doing.

Anyway, I don't know if that helped explain it any better. There are certainly better ways to set a datetime to a specific time, but this way served its purpose of distracting the reading, so they wouldn't notice the real problem is dividing by 60 instead of 60.0

Ben
Post #926917
Posted Monday, May 24, 2010 9:31 AM
SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Thursday, July 10, 2014 1:08 PM
Points: 1,807, Visits: 477
Trey,

The purpose of this question of the day was to show that sometimes when looking at complicated code, the bug or problem can be something simple. If you never have to run into this type of code at your work place I think that is great. That is not my situation, so I deal with the hand that has been given me.

Ben
Post #926919
« Prev Topic | Next Topic »

Add to briefcase ««12345»»»

Permissions Expand / Collapse