none
Convert Time in Hours and Minutes to Decimal using T-SQL

    Question

  • I am trying to convert hours and minutes to decimal and arrive at a sum of time taken.
    can any one provide me with the code.
     The column TotalTimeSpent is the diff in hours/mins between the Started and Ended times.

    SELECT DATENAME(weekday, Started) AS Day,   
        C.Category,
        ClientCode,Description,
        dbo.FormatDateTime(Started, 'HH:MMTongue TiedS 12') as Started,
        dbo.FormatDateTime(Ended, 'HH:MMTongue TiedS 12') as Ended,
    CONVERT(varchar,TimeTaken,108) AS TotalTimeSpent
    FROM dbo.Journal J
    LEFT OUTER JOIN dbo.Categories C ON
    J.CategoryID = C.CategoryID
    WHERE
    --DATENAME(weekday, Started) =@Weekday
     AND
    Started >= @StartDate
    ORDER BY Day, Category, Started
    Wednesday, July 30, 2008 3:43 PM

Answers

  • You might find this helpful...

     

    Code Snippet

    declare @hms varchar(8)
    set @hms = '10:30:00'
    declare @hours int
    declare @minutes decimal
    set @hours = datepart(hour, @hms)
    set @minutes = datepart(minute, @hms)

     

    select @minutes/60+@hours

     

     

     

    Wednesday, July 30, 2008 4:13 PM
  •  

    If you are just using Hours Minutes and Seconds...

    Value = Hours +( (Minutes + Seconds/60)/60)

    If you are just using Hours and Minutes
    Value = Hours + (Minutes/60)

    Wednesday, July 30, 2008 4:18 PM

All replies

  • You might find this helpful...

     

    Code Snippet

    declare @hms varchar(8)
    set @hms = '10:30:00'
    declare @hours int
    declare @minutes decimal
    set @hours = datepart(hour, @hms)
    set @minutes = datepart(minute, @hms)

     

    select @minutes/60+@hours

     

     

     

    Wednesday, July 30, 2008 4:13 PM
  •  

    If you are just using Hours Minutes and Seconds...

    Value = Hours +( (Minutes + Seconds/60)/60)

    If you are just using Hours and Minutes
    Value = Hours + (Minutes/60)

    Wednesday, July 30, 2008 4:18 PM