# 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
• Dear Jon,

I am new to SQL.

I have implemented the same as you said but I am getting an error.

My code.

```ALTER FUNCTION GETDECOT(@STROTHRS NVARCHAR(8))
RETURNS float
AS
BEGIN
DECLARE @RESULT float
declare @hms nvarchar(8)
set @hms = @STROTHRS
declare @hours int
declare @minutes decimal
set @hours = datepart(hour, @hms)
set @minutes = datepart(minute, @hms)
select @RESULT = convert(float,( @minutes/60+@hours))

RETURN @minutes/60+@hours
ENDOr the other way...================================================
ALTER FUNCTION GETDECOT(@STROTHRS NVARCHAR(8))
RETURNS float
AS
BEGIN
DECLARE @RESULT float
declare @hms nvarchar(8)
set @hms = @STROTHRS
declare @hours int
declare @minutes decimal
set @hours = datepart(hour, @hms)
set @minutes = datepart(minute, @hms)
select @RESULT = convert(float,( @minutes/60+@hours))

RETURN @RESULT
END```

QUERY & Error:

```select dbo.GETDECOT(STRINGHOURS) from TIMETBL

ERROR:
Conversion failed when converting date and/or time from character string.

```

VSVAS

• Edited by Saturday, October 17, 2015 12:44 PM
Saturday, October 17, 2015 12:42 PM
• I tested the UDF-s designed by you. Both works.

```CREATE FUNCTION GETDECOT(@STROTHRS NVARCHAR(8))
RETURNS float
AS
BEGIN
DECLARE @RESULT float
declare @hms nvarchar(8)
set @hms = @STROTHRS
declare @hours int
declare @minutes decimal
set @hours = datepart(hour, @hms)
set @minutes = datepart(minute, @hms)
select @RESULT = convert(float,( @minutes/60+@hours))

RETURN @minutes/60+@hours
END
GO

CREATE FUNCTION GETDECOT1(@STROTHRS NVARCHAR(8))
RETURNS float
AS
BEGIN
DECLARE @RESULT float
declare @hms nvarchar(8)
set @hms = @STROTHRS
declare @hours int
declare @minutes decimal
set @hours = datepart(hour, @hms)
set @minutes = datepart(minute, @hms)
select @RESULT = convert(float,( @minutes/60+@hours))

RETURN @RESULT
END
GO

declare @hms varchar(8);
set @hms = '10:30:00';
SELECT dbo.GETDECOT( @hms);
GO
-- 10.5

declare @hms varchar(8);
set @hms = '14:45:00';
SELECT dbo.GETDECOT1( @hms);
GO
-- 14.75

```
Datetime functions: http://www.sqlusa.com/bestpractices/datetimeconversion/

Kalman Toth Database & OLAP Architect Artificial Intelligence
New Book / Kindle: Beginner Database Design & SQL Programming Using Microsoft SQL Server 2014

Saturday, October 17, 2015 2:24 PM
• isnt this enough?

```SELECT DATENAME(weekday, Started) AS Day,
C.Category,
ClientCode,Description,
DATEDIFF(mi,0,Started)/60.0 AS Started,
DATEDIFF(mi,0,Ended)/60.0 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```