T-SQL

Question

T-SQL

Ron McCullough

SSC Guru

Points: 63877
More actions
June 3, 2010 at 9:51 pm

#219673

Comments posted to this topic are about the item T-SQL
If everything seems to be going well, you have obviously overlooked something.
Ron

Please help us, help you -before posting a question please read[/url]
Before posting a performance problem please read[/url]

Viewing 15 posts - 1 through 15 (of 19 total)

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

UMG Developer SSChampion Points: 13482 More actions · Answer 1

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.

malleswarareddy_m SSCertifiable Points: 5847 More actions · Answer 2

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)

UMG Developer SSChampion Points: 13482 More actions · Answer 3

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

deepak.a SSCertifiable Points: 5330 More actions · Answer 4

Thanks a lot for the Question , i learned some thing new 🙂

tommyh SSCertifiable Points: 6302 More actions · Answer 5

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?

Koen Verbeeck SSC Guru Points: 259085 More actions · Answer 6

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

sharath.chalamgari SSCertifiable Points: 5680 More actions · Answer 7

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

Hugo Kornelis SSC Guru Points: 64780 More actions · Answer 8

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.

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/

marklegosz SSCrazy Points: 2816 More actions · Answer 9

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

Daniel Bowlin SSC-Dedicated Points: 34566 More actions · Answer 10

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.

Cliff Jones SSChampion Points: 10547 More actions · Answer 11

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.

Ron McCullough SSC Guru Points: 63877 More actions · Answer 12

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.

If everything seems to be going well, you have obviously overlooked something.

Ron

Please help us, help you -before posting a question please read[/url]
Before posting a performance problem please read[/url]

UMG Developer SSChampion Points: 13482 More actions · Answer 13

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.

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 14

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.