InstantForum.NET v2.9.0SQLServerCentralhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comSun, 19 May 2013 15:29:58 GMT20http://www.sqlservercentral.com/Forums/Topic926485-2611-1.aspxI thought I'd sneak in a quick question or 2 while I was at work...then all that date math took 10 minutes to work out, and now its home time! Not a bad result :-)Wed, 30 Mar 2011 00:38:11 GMTscottm30http://www.sqlservercentral.com/Forums/Topic926485-2611-1.aspxGlad you worked through it and figued it out.BenTue, 01 Jun 2010 21:16:14 GMTbkubicekhttp://www.sqlservercentral.com/Forums/Topic926485-2611-1.aspxWow, all that date math made me think... But the division part was easy.Thanks for the question!Tue, 01 Jun 2010 20:38:16 GMTUMG Developerhttp://www.sqlservercentral.com/Forums/Topic926485-2611-1.aspxSSC Veteran, great question. It was interesting on several levels. First, I answered correctly with an incorrect answer, had to laugh to myself. 90 / 60 is not 1.0000 but 1.5. Second, generating 1.000 as the result indicated that something was wrong with the code, which is what I think you were trying to show us all along. If you want accuracy, which I know we all here strive for, do the math with like data types. “Hall of Fame”, don’t feel bad about using SSMS. You’re the programmer I want working in my shop. Someone that doesn’t waste time trying to prove they can solve the puzzle in their head but uses their head and the tools Microsoft\SQL Server give you. Dear “SSCommitted” skip the paper. Have someone show you how to cut/ paste and use SQL Server Management Studio’s debugger. Make sure you take SSC Veteran’s suggestion of adding a SELECT @hourDiff statement to the sample code before using the debugger.Thanks again SSC Veteran for the great question.Mon, 31 May 2010 21:42:23 GMThgsmith2http://www.sqlservercentral.com/Forums/Topic926485-2611-1.aspx[quote][b]Tom.Thomson (5/31/2010)[/b][hr]But I'm a bit surprised you found these ones complex.[/quote]With hindsight, I could have and should have worked it out by hand. But as you said - the horizontal scrolling and parenthesis counting made my head hurt. If I had checked the site later in the day, I probably would have done it by hand.[quote]If you really want accuracy, don't you have to use CAST(60 as numeric(10,5)) to keep the error below 0.00006? Using CAST(60 as numeric(10,4)) does the division and truncates the result; subsequently that is rounded but this has no effect as it's rounding a numeric(10,4) value to numeric(10,4). (Another reason to dislike SQL's approach to type conversion, truncation, and rounding for expressions on the RHS of assignments.)[/quote]When dividing by numeric(10,4), the result is not numeric(10,4). The rules for precision and scale of the result of arithmetic operations are quite complex. And documented in Books Online.Mon, 31 May 2010 12:21:44 GMTHugo Kornelishttp://www.sqlservercentral.com/Forums/Topic926485-2611-1.aspx[quote][b]Hugo Kornelis (5/26/2010)[/b][hr]The difference will never be more than 0.0001, which admittedly is not significant in most cases. But in those cases where that difference is important, the difference between "60.0" and "CAST(60 AS numeric(10,4))" does matter. [/quote]If you really want accuracy, don't you have to use CAST(60 as numeric(10,5)) to keep the error below 0.00006? Using CAST(60 as numeric(10,4)) does the division and truncates the result; subsequently that is rounded but this has no effect as it's rounding a numeric(10,4) value to numeric(10,4). (Another reason to dislike SQL's approach to type conversion, truncation, and rounding for expressions on the RHS of assignments.)Mon, 31 May 2010 12:16:19 GMTL' Eomot Inverséhttp://www.sqlservercentral.com/Forums/Topic926485-2611-1.aspx[quote][b]Hugo Kornelis (5/24/2010)[/b][hr]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. [/quote]I don't count using the system to simplify complex expressions for you as cheating, in fact if the expressions are complex enough I count not doing so as stupidity. But I'm a bit surprised you found these ones complex. Yes, it's irritating to have to scroll horizontally, and to see the bracket matches so that you know what goes with what, and it'd bad coding not to lay it out cleanly so that the structure of each expression is obvious, but it doesn't really take much time to notice that the only differences in the two long expressions are substitution or 0 for 30 in the minutes and 10 for 12 in the hours, and that the bracketing is particularly simple, so that the differences is 1H30M (this took me less than a minute - possibly because last year I looked at a lot of code that does lots of bulk precalculation of aggregates and percentiles for various periods and it was riddled with start-time and end-time expressions much like these ones, but I can't see it taking me more than 2 mins even if I hadn't had that experience).Mon, 31 May 2010 12:00:14 GMTL' Eomot Inverséhttp://www.sqlservercentral.com/Forums/Topic926485-2611-1.aspx[quote][b]Z.A.T (5/27/2010)[/b][hr]I run solved this query then I answerd NULL, because you forget to write select @hourDiff in your query.so my answer is write NULL. if you wrote select @hourDiff at the last line then it will return 1.[/quote]One might argue that the blame lies elsewhere, and the 'crime' committed was not reading the question properly.Attention to detail is often a desirable trait in DBAs...My other thought is that omitting the SELECT statement made it easier to get wrong for those people who just copy the code and execute it.Thu, 27 May 2010 04:18:45 GMTPaul Whitehttp://www.sqlservercentral.com/Forums/Topic926485-2611-1.aspxI run solved this query then I answerd NULL, because you forget to write select @hourDiff in your query.so my answer is write NULL. if you wrote select @hourDiff at the last line then it will return 1.Thu, 27 May 2010 04:13:08 GMTZ.A.Thttp://www.sqlservercentral.com/Forums/Topic926485-2611-1.aspxPut the below statment at the end of the code and rerun it.select @hourDiffWed, 26 May 2010 13:18:56 GMTyidnek T Beyenehttp://www.sqlservercentral.com/Forums/Topic926485-2611-1.aspxPut select @hourDiff at the end of the code and rerun it.Wed, 26 May 2010 13:08:04 GMTyidnek T Beyenehttp://www.sqlservercentral.com/Forums/Topic926485-2611-1.aspx[quote][b]The Dixie Flatline (5/26/2010)[/b][hr]I got it wrong. I answered "purple". :doze:[/quote]Family Feud Style:"Good Answer, Good Answer!!":cool:Wed, 26 May 2010 12:36:19 GMTSQLRNNRhttp://www.sqlservercentral.com/Forums/Topic926485-2611-1.aspx[quote][b]The Dixie Flatline (5/26/2010)[/b][hr]I got it wrong. I answered "purple". :doze:[/quote]*sound of a mouthful of tea hitting an LCD screen at high speed*Wed, 26 May 2010 12:29:06 GMTPaul Whitehttp://www.sqlservercentral.com/Forums/Topic926485-2611-1.aspxI got it wrong. I answered "purple". :doze:Wed, 26 May 2010 12:26:28 GMTThe Dixie Flatlinehttp://www.sqlservercentral.com/Forums/Topic926485-2611-1.aspxWhat Hugo said. :-)Wed, 26 May 2010 12:16:47 GMTPaul Whitehttp://www.sqlservercentral.com/Forums/Topic926485-2611-1.aspx[quote][b]Rajasekhar Reddy (5/26/2010)[/b][hr]Hi,select command in the code is used for assginign variables not to print any output.the given code won't return any value.Thanks,RajaSekhar Reddy .K[/quote]That is entirely correct. Of course, the question was "what result will @hourdiff hold?", not "what will be returned when you run this code", so I fail to see the significance of this observation.Wed, 26 May 2010 00:59:04 GMTHugo Kornelishttp://www.sqlservercentral.com/Forums/Topic926485-2611-1.aspx[quote][b]bkubicek (5/25/2010)[/b][hr]Hugo,It is the precision of the variable you are putting the value into that matter. @min is a numeric(10,4), so you will get your four decimal places using plain old 60.0.Ben[/quote]I assume you meant to write that @hourDiff is nummeric(10,4); @min is an integer. If that is what you meant, then you are right that the end result will always be converted to numeric(10,4). But intermediate results use a different precision; the casting to the target data type of numeric(10, 4) is the final step.Run the following code to see how the data types used for the operands of the division affect the length and precision of the intermediate result. In this case, since the division result is exactly 1.5, there will not be any net effect. But there are cases where the number of decimals used in the intermediate result may affect the end result. (Keep in mind that the division result will be [b]truncated[/b] to the precision of the intermediate result, which will then be [i]rounded[/i] to the precision of the variable).The difference will never be more than 0.0001, which admittedly is not significant in most cases. But in those cases where that difference is important, the difference between "60.0" and "CAST(60 AS numeric(10,4))" does matter.Wed, 26 May 2010 00:57:55 GMTHugo Kornelishttp://www.sqlservercentral.com/Forums/Topic926485-2611-1.aspxHi,select command in the code is used for assginign variables not to print any output.the given code won't return any value.Thanks,RajaSekhar Reddy .KWed, 26 May 2010 00:15:57 GMTRajasekhara Reddyhttp://www.sqlservercentral.com/Forums/Topic926485-2611-1.aspxHugo,It is the precision of the variable you are putting the value into that matter. @min is a numeric(10,4), so you will get your four decimal places using plain old 60.0.BenTue, 25 May 2010 17:13:26 GMTbkubicekhttp://www.sqlservercentral.com/Forums/Topic926485-2611-1.aspx[quote][b]bkubicek (5/25/2010)[/b][hr]Why wouldn't you just divide by 60.0?As soon as you add the .0 is becomes numeric.Ben[/quote]If numeric(3,1) works, then 60.0 is as good as any of the other options. If you need the precision of a numeric(10,4), the explicit cast is the way to go.Tue, 25 May 2010 17:08:34 GMTHugo Kornelishttp://www.sqlservercentral.com/Forums/Topic926485-2611-1.aspxWhy wouldn't you just divide by 60.0?As soon as you add the .0 is becomes numeric.BenTue, 25 May 2010 17:05:31 GMTbkubicekhttp://www.sqlservercentral.com/Forums/Topic926485-2611-1.aspx[quote][b]marklegosz (5/25/2010)[/b][hr]The integer division thing in general for me is often one of those "so simple you missed it situations". I'd be interested in what is considered best practice in real life for doing this properly.To use this example:[code="sql"]select @hourDiff = @min / cast(60 as numeric(10,4))[/code]and [code="sql"]select @hourDiff = cast(@min as numeric(10,4)) / 60[/code]Both give me 1.5. Which one of these options is the best way to go, or is there a better way of handling this issue?[/quote]There is no real difference between the two. As soon as one of the operands for the diivision is numeric, the other will be converted to numeric as well. You can use what pleases your eyes the most.Tue, 25 May 2010 17:04:26 GMTHugo Kornelishttp://www.sqlservercentral.com/Forums/Topic926485-2611-1.aspxThe integer division thing in general for me is often one of those "so simple you missed it situations". I'd be interested in what is considered best practice in real life for doing this properly.To use this example:[code="sql"]select @hourDiff = @min / cast(60 as numeric(10,4))[/code]and [code="sql"]select @hourDiff = cast(@min as numeric(10,4)) / 60[/code]Both give me 1.5. Which one of these options is the best way to go, or is there a better way of handling this issue?Tue, 25 May 2010 14:26:43 GMTmarklegoszhttp://www.sqlservercentral.com/Forums/Topic926485-2611-1.aspx[quote][b]Kenneth J. Moore (5/24/2010)[/b][hr]I really just guessed after about 30 seconds of trying to follow the logic. Only off by 1.0000![/quote]Yeah that was me too. I looked at the date parsing stuff, got bored and skipped it, guessed it would be an integer division thing and took a guess at zero.Tue, 25 May 2010 02:38:42 GMTKevin Gillhttp://www.sqlservercentral.com/Forums/Topic926485-2611-1.aspxI really just guessed after about 30 seconds of trying to follow the logic. Only off by 1.0000!Mon, 24 May 2010 15:17:45 GMTKenneth J. Moorehttp://www.sqlservercentral.com/Forums/Topic926485-2611-1.aspx[quote][b]bkubicek (5/24/2010)[/b][hr]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[/quote]Definately do another QOD. Don't pay too much attention to my complaining. ;-) Honestly I really appreciate the effort of the people who come up with these questions.Mon, 24 May 2010 14:47:21 GMTTrey Stakerhttp://www.sqlservercentral.com/Forums/Topic926485-2611-1.aspx[quote][b]Hugo Kornelis (5/24/2010)[/b][hr][quote][b]CirquedeSQLeil (5/24/2010)[/b][hr]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.[/quote]+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...)[/quote]Thanks Hugo.Mon, 24 May 2010 14:32:44 GMTSQLRNNRhttp://www.sqlservercentral.com/Forums/Topic926485-2611-1.aspx[quote][b]CirquedeSQLeil (5/24/2010)[/b][hr]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.[/quote]+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...)Mon, 24 May 2010 14:31:16 GMTHugo Kornelishttp://www.sqlservercentral.com/Forums/Topic926485-2611-1.aspx[quote][b]bkubicek (5/24/2010)[/b][hr]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[/quote]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)Mon, 24 May 2010 14:27:53 GMTHugo Kornelishttp://www.sqlservercentral.com/Forums/Topic926485-2611-1.aspxThanks, I am glad you put forth the effort and were rewarded for it.BenMon, 24 May 2010 11:42:02 GMTbkubicekhttp://www.sqlservercentral.com/Forums/Topic926485-2611-1.aspxGreat 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.Mon, 24 May 2010 11:38:28 GMTKevinC.http://www.sqlservercentral.com/Forums/Topic926485-2611-1.aspxI see both sides of it. You, Trey and Hugo all have valid points.Please, don't let that discourage you from submitting more questions.Mon, 24 May 2010 10:43:27 GMTSQLRNNRhttp://www.sqlservercentral.com/Forums/Topic926485-2611-1.aspxYou are welcome. Thanks for not complaining about the formatting. If I do another Question of the day I will have to watch my formatting.BenMon, 24 May 2010 10:39:35 GMTbkubicekhttp://www.sqlservercentral.com/Forums/Topic926485-2611-1.aspxThanks for the QODMon, 24 May 2010 10:36:28 GMTSQLRNNRhttp://www.sqlservercentral.com/Forums/Topic926485-2611-1.aspxTrey,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.BenMon, 24 May 2010 10:21:00 GMTbkubicekhttp://www.sqlservercentral.com/Forums/Topic926485-2611-1.aspx[quote][b]bkubicek (5/24/2010)[/b][hr]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[/quote]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.Mon, 24 May 2010 10:08:28 GMTTrey Stakerhttp://www.sqlservercentral.com/Forums/Topic926485-2611-1.aspx[quote][b]bkubicek (5/24/2010)[/b][hr]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.0Ben[/quote]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,webrunnerMon, 24 May 2010 09:37:13 GMTwebrunnerhttp://www.sqlservercentral.com/Forums/Topic926485-2611-1.aspxTrey,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.BenMon, 24 May 2010 09:31:55 GMTbkubicekhttp://www.sqlservercentral.com/Forums/Topic926485-2611-1.aspxWebrunner,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.0BenMon, 24 May 2010 09:28:59 GMTbkubicekhttp://www.sqlservercentral.com/Forums/Topic926485-2611-1.aspx[quote][b]Hugo Kornelis (5/24/2010)[/b][hr]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 [url=http://www.karaszi.com/SQLServer/info_datetime.asp]ultimate guide to the datetime datatypes[/url]), 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:[code="sql"]SET @startDt = '2010-05-01T10:30:00';SET @endDt = '2010-05-01T12:00:00';[/code]Then I would have been able to answer the question without cheating, and felt even better about my point.[/quote]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.Mon, 24 May 2010 09:21:35 GMTTrey Staker