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 «««23456

What will this return? Expand / Collapse
Author
Message
Posted Monday, May 31, 2010 12:00 PM


SSCrazy Eights

SSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy Eights

Group: General Forum Members
Last Login: Yesterday @ 10:00 AM
Points: 8,551, Visits: 9,043
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
Post #930449
Posted Monday, May 31, 2010 12:16 PM


SSCrazy Eights

SSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy Eights

Group: General Forum Members
Last Login: Yesterday @ 10:00 AM
Points: 8,551, Visits: 9,043
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
Post #930455
Posted Monday, May 31, 2010 12:21 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 6:40 AM
Points: 5,916, Visits: 8,166
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
Post #930458
Posted Monday, May 31, 2010 9:42 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, March 31, 2011 8:49 PM
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.
Post #930515
Posted Tuesday, June 1, 2010 8:38 PM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Monday, June 16, 2014 9:38 AM
Points: 2,163, Visits: 2,189
Wow, all that date math made me think... But the division part was easy.

Thanks for the question!
Post #931070
Posted Tuesday, June 1, 2010 9:16 PM
SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Thursday, July 10, 2014 1:08 PM
Points: 1,807, Visits: 477
Glad you worked through it and figued it out.

Ben
Post #931074
Posted Wednesday, March 30, 2011 12:38 AM
SSChasing Mays

SSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing Mays

Group: General Forum Members
Last Login: Tuesday, December 3, 2013 4:40 PM
Points: 654, Visits: 375
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
Post #1086006
« Prev Topic | Next Topic »

Add to briefcase «««23456

Permissions Expand / Collapse