# 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:MMS 12') as Started,
dbo.FormatDateTime(Ended, 'HH:MMS 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

• 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