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```

Visakh
----------------------------
My Wiki User Page
My MSDN Page
My Personal Blog

Saturday, October 17, 2015 4:41 PM
• This mostly works, but it's rounding to whole numbers for me. I needed more precision.

If you need precision for decimal places, add the number of decimal places to the denominator. i.e. divide by 60.00 instead.

Not sure why SQL Service requires that, but that's what I've found w/ the version I'm using.

Quick test in SSMS:

SELECT 30/60

Result: 0

SELECT 30/60.00

Result: 0.50

Tuesday, June 13, 2017 11:18 PM