May 24, 2010 at 8:54 am
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?"
May 24, 2010 at 9:12 am
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
-------------------
A SQL query walks into a bar and sees two tables. He walks up to them and asks, "Can I join you?"
Ref.: http://tkyte.blogspot.com/2009/02/sql-joke.html
May 24, 2010 at 9:21 am
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
May 24, 2010 at 9:28 am
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
May 24, 2010 at 9:31 am
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
May 24, 2010 at 9:37 am
bkubicek (5/24/2010)
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
Thanks, Ben. I will use your explanation to try to work out the question manually. I guess the millisecond part scared me off of trying to run through it manually, but I will use the method you describe and just make up a millisecond value to start off with.
Thanks again,
webrunner
-------------------
A SQL query walks into a bar and sees two tables. He walks up to them and asks, "Can I join you?"
Ref.: http://tkyte.blogspot.com/2009/02/sql-joke.html
May 24, 2010 at 10:08 am
bkubicek (5/24/2010)
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
Interesting point however, I do the QOD because I enjoy the QOD not because I want to clean up the questioners poorly formated code. I clean up code left by former employees or contractors all day long because I'm PAID to do that. Nobody gets paid to do the QOD. Could you imagine doing the New York Times puzzle but instead of getting the nicely formatted puzzle they put the puzzle throughout the news paper or intentionally make it difficult to read. Hmmm maybe they could blur the ink on purpose to show a point.
Thanks for submitting the question, I did like the problem just not the code formatting. B+ on the question, C- on the formatting.
---------------------------------------------------------------------
Use Full Links:
KB Article from Microsoft on how to ask a question on a Forum
May 24, 2010 at 10:21 am
Trey,
Fair enough. I guess I have seen a lot harder questions of the day and I guess the formatting didn't seem that hard to fix to me. I clicked return a couple of times in my post above to webrunner. Perhaps that format is still too messy.
All the same, you are entitled to your opinion and I hope you have a great day.
Ben
May 24, 2010 at 10:36 am
Thanks for the QOD
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
May 24, 2010 at 10:39 am
You are welcome.
Thanks for not complaining about the formatting.
If I do another Question of the day I will have to watch my formatting.
Ben
May 24, 2010 at 10:43 am
I see both sides of it. You, Trey and Hugo all have valid points.
Please, don't let that discourage you from submitting more questions.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
May 24, 2010 at 11:38 am
Great question.
I personally enjoy the questions that require more thought. This was enjoyable for me as I was able to 'troubleshoot' the code and then see the simple division at the end.
It made me happy that I worked through it right.
Thanks again.
May 24, 2010 at 11:42 am
Thanks, I am glad you put forth the effort and were rewarded for it.
Ben
May 24, 2010 at 2:27 pm
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
Hi Ben,
Thanks for your reply. This eplanation makes me understand a lot better where you were coming from with this question. (And you even inspired me to submit four more questions, so a lot of good has come from it!)
I now feel bad for cheating. Maybe I should have waited until after my morning coffee, when I am more able to work my way through this code.
Oh, and indeed, I am fortunate enough that the only code I have to maintain at work is the code I wrote myself previously. (Though I still get my fair share of illegible legacy stuff when helping in newsgroups and forums)
May 24, 2010 at 2:31 pm
CirquedeSQLeil (5/24/2010)
I see both sides of it. You, Trey and Hugo all have valid points.Please, don't let that discourage you from submitting more questions.
+1 on all points made by Jason (especially the "don't let that discourage" part - this audience has a tendency to scare off those who contribute freely...)
Viewing 15 posts - 16 through 30 (of 57 total)
You must be logged in to reply to this topic. Login to reply