What will this return?

Question

Post reply

What will this return?

Viewing 13 posts - 46 through 57 (of 57 total)

You must be logged in to reply to this topic. Login to reply

SQLRNNR SSC Guru Points: 281334 More actions · Answer 1

The Dixie Flatline (5/26/2010)
I got it wrong. I answered "purple". :doze:

Family Feud Style:

"Good Answer, Good Answer!!":cool:

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

yidnek T Beyene Old Hand Points: 386 More actions · Answer 2

Put

select @hourDiff

at the end of the code and rerun it.

yidnek T Beyene Old Hand Points: 386 More actions · Answer 3

Put the below statment at the end of the code and rerun it.

select @hourDiff

Z.A.T SSC Enthusiast Points: 135 More actions · Answer 4

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.

Paul White SSC Guru Points: 150467 More actions · Answer 5

Z.A.T (5/27/2010)
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.

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.

Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi

TomThomson SSC Guru Points: 104773 More actions · Answer 6

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 SSC Guru Points: 104773 More actions · Answer 7

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 SSC Guru Points: 64790 More actions · Answer 8

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/Data Platform MVP (2006-2016)
Visit my SQL Server blog: https://sqlserverfast.com/blog/
SQL Server Execution Plan Reference: https://sqlserverfast.com/epr/

hgsmith2 SSC Rookie Points: 36 More actions · Answer 9

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 SSChampion Points: 13482 More actions · Answer 10

Wow, all that date math made me think... But the division part was easy.

Thanks for the question!

bkubicek SSChampion Points: 10948 More actions · Answer 11

bkubicek

SSChampion

Points: 10948

June 1, 2010 at 9:16 pm

#1175079

Glad you worked through it and figued it out.

Ben

scottm30 Ten Centuries Points: 1094 More actions · Answer 12

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 SSCertifiable Points: 6930 More actions · Answer 13

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.