Post reply

Working with Numbers

  • March 29, 2007 at 1:08 pm

    #194811

    I need to calculate the number of elapsed hours from a given time to 18:00:00.  Let's say the given time is 15:01:00; the expected result should be 18 - 15.01666 = 2.983334.  However in SQL it gives me a different value

    declare @d1 datetime

    set @d1 = '2007-01-12 15:01:50.863'

    select cast(datepart(hh,@d1) as real) + cast(cast(datepart(mi,@d1) as real) / cast (60 as real) as real)

    select 18 - cast(datepart(hh,@d1) as real) + cast(cast(datepart(mi,@d1) as real) / cast (60 as real) as real)

    The result is  3.016666.  What do I need to change in order to get the expected number?

    Thanks.

  • March 29, 2007 at 1:29 pm

    #697735

    Try the following:

    declare @d1 datetime,

            @d2 datetime

    set @d1 = cast('2007-01-12 15:01:50.863' as datetime)

    set @d2 = cast('18:00:00.000 as datetime)

    select datediff(ss, @d2, cast(convert(varchar(12), @d1, 114) as datetime)/3600.0

    hth

  • March 29, 2007 at 2:22 pm

    #697749

    thanks for the reply.  but do you know why my query didn't work?

  • March 29, 2007 at 2:31 pm

    #697750

    Because you are adding minutes instead of subtracting them.

    _____________
    Code for TallyGenerator

  • March 29, 2007 at 2:41 pm

    #697753

    I would say because you were trying to make it more complicated than it really was.  You probably could have gotten your way to work, but you should should have broken it down into smaller pieces and built from there.  Start with hours, then move to minutes, then seconds, etc.

    I just thought it would be easier to allow SQL to do the math using the available data functions, that way I didn't have to think as hard.

  • March 29, 2007 at 3:13 pm

    #697760

    Here is an explanation of how DATEDIFF works. It applies to hours as well.

    http://www.sqlteam.com/item.asp?ItemID=26922

     

    N 56°04'39.16"
    E 12°55'05.25"

  • March 29, 2007 at 9:13 pm

    #697795 

    select
 Hours = datediff(ms,T1,T2)/3600000.000
from
 (selectT1 = convert(datetime,'15:01:00'),
  T2 = convert(datetime,'18:00:00') ) a
    Result:
    Hours                      
-------------------------- 
2.98333333333
    (1 row(s) affected)

Viewing 7 posts - 1 through 6 (of 6 total)

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