June 3, 2010 at 10:36 pm
Thanks for the question, I learned something.
I have to say putting the T-SQL as a graphic made it hard to answer because I couldn't clearly make out all the numbers even after zooming in. Though I do understand why you did it, to prevent easy execution of the code, but maybe it would be good to try to avoid scaling and anti-aliasing so that the code can easily be read.
June 3, 2010 at 11:01 pm
I got wrong.First I checked for hours and then i thought 7.and guess that with additon of minutes into hours ,answer is 8. and i did not checked minutes.start minutes are greater than end minutes.Small mistake.but it shows to pay attention before answering the question.
Malleswarareddy
I.T.Analyst
MCITP(70-451)
June 3, 2010 at 11:11 pm
I didn't even notice that, I looked at what the code was trying to accomplish and calculated the difference for each, I saw 15 minutes between so I put that line as 0.25. I didn't think about it being 1 hour and then -0.75 from the minutes. Which turns out to be the same thing so it worked out for me. I was concentrating on the casting, the rounding, and actually being able to read the numbers. 🙂
June 3, 2010 at 11:31 pm
Thanks a lot for the Question , i learned some thing new 🙂
June 3, 2010 at 11:57 pm
Okay so now you can use ODBC functions inside SQL. So learnt something new and thus the purpose off the question is fulfilled (now that was a hard word to spell to).
However...
A quick glance over the available functions and as far as i can tell most (all) have a SQL counterpart (or something that you can easily recreate using a few SQL functions) .
So why use the ODBC variant instead of the SQL variant?
June 4, 2010 at 1:38 am
Great question, but hard to read. 🙂
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
June 4, 2010 at 2:46 am
Good Question:-)
it is hard to read
If any one is answered incorrectly because of the visibility they can use this code
Create Table #s(start datetime,[end] datetime)
Insert Into #s
Select Cast ('2010-04-08 12:00:00'as Datetime),Cast ('2010-04-08 14:10:00'as Datetime)
Union all
Select Cast ('2010-04-08 13:00:00'as Datetime),Cast ('2010-04-08 14:00:00'as Datetime)
Union all
Select Cast ('2010-04-08 15:45:00'as Datetime),Cast ('2010-04-08 16:00:00'as Datetime)
Union all
Select Cast ('2010-04-08 15:00:00'as Datetime),Cast ('2010-04-08 18:00:00'as Datetime)
select * From #s
select Round(
Cast(Sum(({fn HOUR([end])} - {fn HOUR(start)})) as Decimal (4,2))
+
Cast(Sum(({fn MINUTE([end])} - {fn MINUTE(start)})) as Decimal (4,2))/60
,4
) From #S
June 4, 2010 at 4:31 am
Good question, Ron.
I seriously hope that nobody ever uses the ODBC date functions in real code, since (as tommyh mentions) they all have T-SQL counterparts.
June 4, 2010 at 7:08 am
I liked the SQL as a graphic - stopped me from either running the code or adjusting the layout to read better. I actually feel better about having gotten it right than I normally would have (i.e. more of a challenge!)
June 4, 2010 at 7:53 am
I was unfamiliar with the HOUR and MINUTE functions, so I had to do some reading before answering the question. Thanks for helping me learn something.
June 4, 2010 at 8:08 am
tommyh (6/3/2010)
Okay so now you can use ODBC functions inside SQL. So learnt something new and thus the purpose off the question is fulfilled (now that was a hard word to spell to).However...
A quick glance over the available functions and as far as i can tell most (all) have a SQL counterpart (or something that you can easily recreate using a few SQL functions) .
So why use the ODBC variant instead of the SQL variant?
Interesting question, I didn't know you could do that. But yes I agree I am not sure why I would want to do that.
June 4, 2010 at 8:30 am
marklegosz
I liked the SQL as a graphic - stopped me from either running the code or adjusting the layout to read better. I actually feel better about having gotten it right than I normally would have (i.e. more of a challenge!)
Ah ha, finally someone recognized why I posted it as a graphic... Thanks for noticing that and acknowledging same.
Hugo Kornelis
I seriously hope that nobody ever uses the ODBC date functions in real code, since (as tommyh mentions) they all have T-SQL counterparts
I must agree with you, my objective in composing the question was to illustrate for one and all the vast scope and capabilities, obvious and hidden, of SQL Server.
June 4, 2010 at 8:59 am
bitbucket-25253 (6/4/2010)
marklegosz
I liked the SQL as a graphic - stopped me from either running the code or adjusting the layout to read better. I actually feel better about having gotten it right than I normally would have (i.e. more of a challenge!)
Ah ha, finally someone recognized why I posted it as a graphic... Thanks for noticing that and acknowledging same.
I agree with the not just running the code, but I don't agree with the making it difficult to re-format. Often when looking at another person's code the first thing I do is re-format it with SQL Prompt to make it easier to read.
June 4, 2010 at 9:55 am
Hugo Kornelis (6/4/2010)
Good question, Ron.I seriously hope that nobody ever uses the ODBC date functions in real code, since (as tommyh mentions) they all have T-SQL counterparts.
Now that depends. I have had to convert code originally written in ACCESS that used the ODBC functions to use the T-SQL counterparts. That was my first encounter with ODBC functions in SQL.
Viewing 15 posts - 1 through 15 (of 19 total)
You must be logged in to reply to this topic. Login to reply