T-SQL

  • 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]

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

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

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

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

  • 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?

  • 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

  • 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

  • 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/

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

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

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

  • 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]

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

  • 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