Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


What will this return?


What will this return?

Author
Message
TomThomson
TomThomson
SSChampion
SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)

Group: General Forum Members
Points: 10700 Visits: 11991
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.

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).

Tom

TomThomson
TomThomson
SSChampion
SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)

Group: General Forum Members
Points: 10700 Visits: 11991
Hugo Kornelis (5/26/2010)
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.

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.)

Tom

Hugo Kornelis
Hugo Kornelis
SSCrazy Eights
SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)

Group: General Forum Members
Points: 8319 Visits: 11550
Tom.Thomson (5/31/2010)
But I'm a bit surprised you found these ones complex.

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.

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.)

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.


Hugo Kornelis, SQL Server MVP
Visit my SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis
hgsmith2
hgsmith2
Forum Newbie
Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)

Group: General Forum Members
Points: 8 Visits: 21
SSC 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.
UMG Developer
UMG Developer
SSCrazy
SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)

Group: General Forum Members
Points: 2280 Visits: 2204
Wow, all that date math made me think... But the division part was easy.

Thanks for the question!
bkubicek
bkubicek
SSCrazy
SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)

Group: General Forum Members
Points: 2490 Visits: 757
Glad you worked through it and figued it out.

Ben
scottm30
scottm30
Say Hey Kid
Say Hey Kid (673 reputation)Say Hey Kid (673 reputation)Say Hey Kid (673 reputation)Say Hey Kid (673 reputation)Say Hey Kid (673 reputation)Say Hey Kid (673 reputation)Say Hey Kid (673 reputation)Say Hey Kid (673 reputation)

Group: General Forum Members
Points: 673 Visits: 383
I 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 :-)
Junior Galvão - MVP
Junior Galvão - MVP
SSCrazy
SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)

Group: General Forum Members
Points: 2787 Visits: 210
Hi, I executed this code in SQL Server 2014 and 2008 R2, I got the message "Command(s) completed successfully."

Please check the correct reply.

Thanks, Regards.
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search