# Convert minutes to HH,MM

hI
As you will see from my code below,the result of time worked Total Week is  in Minutes.  Is there a way I can convert the result from minutes to Hrs and Minutes
I am using SQL 2000 (ENTERPRISE MANAGER).
Thank you
``````SELECT     empid, START_MON, LUNCH_OUT_MON, LUNCH_BACK_MON, END_MON, DATEDIFF(n, START_MON, LUNCH_OUT_MON) AS Mon_morning,
DATEDIFF(n, LUNCH_BACK_MON, END_MON) AS Mon_Evening, DATEDIFF(n, START_MON, LUNCH_OUT_MON) + DATEDIFF(n, LUNCH_BACK_MON,
END_MON) AS Tot_Monday, START_TUE, LUNCH_OUT_TUE, LUNCH_BACK_TUE, END_TUE, DATEDIFF(n, START_TUE, LUNCH_OUT_TUE)
AS TUE_morning, DATEDIFF(n, LUNCH_BACK_TUE, END_TUE) AS TUE_Evening, DATEDIFF(n, START_TUE, LUNCH_OUT_TUE) + DATEDIFF(n,
LUNCH_BACK_TUE, END_TUE) AS Tot_TUEday, DATEDIFF(n, START_MON, LUNCH_OUT_MON) + DATEDIFF(n, LUNCH_BACK_MON, END_MON)
+ DATEDIFF(n, START_TUE, LUNCH_OUT_TUE) + DATEDIFF(n, LUNCH_BACK_TUE, END_TUE) AS [total Week]
FROM         dbo.EMPLOYEE_TIMESHEET
``````
###### Who is Participating?

Commented:
I did not even make the slightest attempt to review it.  I took for granted that [total week] was correct and what you wanted and went from there.

My guess is that you are working with what you have.  But I'd bet it could be cleaned up.

I'd open a new Q? though!
0

Commented:
if @mins is your minutes then

select @mins/60 as hours, @mins%60 as minutes

will give you what you want
0

Product SpecialistCommented:
Assuming that Mints is the field holding the number of minutes

Format(Mints \ 60, "0") & ":" & Format(Mints Mod 60, "00")
0

Commented:
maybe use a combination of division and FLOOR() to find the hour and the modulus by 60 operator ( % 60 ) to find the total minutes

e.g.

Floor(Week / 60) AS Hours
(Week % 60) AS Minutes
0

Commented:
I've not looked at ANYTHING else.  All I did was wrap the existing select into a derived table and used a convert/dateadd to get hours/minutes.
``````select *, convert(char(5),dateadd(n, [total week],0),108) TimeInHours
from(
SELECT     empid, START_MON, LUNCH_OUT_MON, LUNCH_BACK_MON, END_MON, DATEDIFF(n, START_MON, LUNCH_OUT_MON) AS Mon_morning,
DATEDIFF(n, LUNCH_BACK_MON, END_MON) AS Mon_Evening, DATEDIFF(n, START_MON, LUNCH_OUT_MON) + DATEDIFF(n, LUNCH_BACK_MON,
END_MON) AS Tot_Monday, START_TUE, LUNCH_OUT_TUE, LUNCH_BACK_TUE, END_TUE, DATEDIFF(n, START_TUE, LUNCH_OUT_TUE)
AS TUE_morning, DATEDIFF(n, LUNCH_BACK_TUE, END_TUE) AS TUE_Evening, DATEDIFF(n, START_TUE, LUNCH_OUT_TUE) + DATEDIFF(n,
LUNCH_BACK_TUE, END_TUE) AS Tot_TUEday, DATEDIFF(n, START_MON, LUNCH_OUT_MON) + DATEDIFF(n, LUNCH_BACK_MON, END_MON)
+ DATEDIFF(n, START_TUE, LUNCH_OUT_TUE) + DATEDIFF(n, LUNCH_BACK_TUE, END_TUE) AS [total Week]
FROM         dbo.EMPLOYEE_TIMESHEET) a
``````
0

Commented:
And I used [total week] because you stated "result of time worked Total Week is  in Minutes"
0

Author Commented:
Hi Brandon
Works great .One problem, when the total Hours are > than 24:00 itwill only show Hrs -24
Code attached. Working with time is a real xxx/xx
Is there any way around this??
Paul
``````SELECT     *, CONVERT(char(5), DATEADD(n, [total Week], 0), 108) AS TimeInHours
FROM         (SELECT     empid, START_MON, LUNCH_OUT_MON, LUNCH_BACK_MON, END_MON, DATEDIFF(n, START_MON, LUNCH_OUT_MON) AS Mon_morning,
DATEDIFF(n, LUNCH_BACK_MON, END_MON) AS Mon_Evening, DATEDIFF(n, START_MON, LUNCH_OUT_MON) + DATEDIFF(n,
LUNCH_BACK_MON, END_MON) AS Tot_Monday, START_TUE, LUNCH_OUT_TUE, LUNCH_BACK_TUE, END_TUE, DATEDIFF(n,
START_TUE, LUNCH_OUT_TUE) AS TUE_morning, DATEDIFF(n, LUNCH_BACK_TUE, END_TUE) AS TUE_Evening, DATEDIFF(n, START_TUE,
LUNCH_OUT_TUE) + DATEDIFF(n, LUNCH_BACK_TUE, END_TUE) AS Tot_TUEday, START_Wed, LUNCH_OUT_Wed, LUNCH_BACK_Wed,
END_Wed, DATEDIFF(n, START_Wed, LUNCH_OUT_Wed) AS Wed_morning, DATEDIFF(n, LUNCH_BACK_Wed, END_Wed)
AS Wed_Evening, DATEDIFF(n, START_Wed, LUNCH_OUT_Wed) + DATEDIFF(n, LUNCH_BACK_Wed, END_Wed) AS Tot_Wedday,
START_Thu, LUNCH_OUT_Thu, LUNCH_BACK_Thu, END_Thu, DATEDIFF(n, START_Thu, LUNCH_OUT_Thu) AS Thu_morning, DATEDIFF(n,
LUNCH_BACK_Thu, END_Thu) AS Thu_Evening, DATEDIFF(n, START_Thu, LUNCH_OUT_Thu) + DATEDIFF(n, LUNCH_BACK_Thu, END_Thu)
AS Tot_Thuday, START_Fri, LUNCH_OUT_Fri, LUNCH_BACK_Fri, END_Fri, DATEDIFF(n, START_Fri, LUNCH_OUT_Fri) AS Fri_morning,
DATEDIFF(n, LUNCH_BACK_Fri, END_Fri) AS Fri_Evening, DATEDIFF(n, START_Fri, LUNCH_OUT_Fri) + DATEDIFF(n, LUNCH_BACK_Fri,
END_Fri) AS Tot_Friday, DATEDIFF(n, START_MON, LUNCH_OUT_MON) + DATEDIFF(n, LUNCH_BACK_MON, END_MON) + DATEDIFF(n,
START_TUE, LUNCH_OUT_TUE) + DATEDIFF(n, LUNCH_BACK_TUE, END_TUE) + DATEDIFF(n, START_Wed, LUNCH_OUT_Wed)
+ DATEDIFF(n, LUNCH_BACK_Wed, END_Wed) + DATEDIFF(n, START_Thu, LUNCH_OUT_Thu) + DATEDIFF(n, LUNCH_BACK_Thu, END_Thu)
+ DATEDIFF(n, START_Fri, LUNCH_OUT_Fri) + DATEDIFF(n, LUNCH_BACK_Fri, END_Fri) AS [total Week]
FROM          dbo.EMPLOYEE_TIMESHEET) a
``````
0

Commented:
You would want to do it the way that http:#22721341 shows.

Here it is put into your query
``````SELECT     *, cast([total Week]/60 as varchar(7))+':'+right('00'+cast([total week]%60as varchar(2)),2) AS TimeInHours
FROM         (SELECT     empid, START_MON, LUNCH_OUT_MON, LUNCH_BACK_MON, END_MON, DATEDIFF(n, START_MON, LUNCH_OUT_MON) AS Mon_morning,
DATEDIFF(n, LUNCH_BACK_MON, END_MON) AS Mon_Evening, DATEDIFF(n, START_MON, LUNCH_OUT_MON) + DATEDIFF(n,
LUNCH_BACK_MON, END_MON) AS Tot_Monday, START_TUE, LUNCH_OUT_TUE, LUNCH_BACK_TUE, END_TUE, DATEDIFF(n,
START_TUE, LUNCH_OUT_TUE) AS TUE_morning, DATEDIFF(n, LUNCH_BACK_TUE, END_TUE) AS TUE_Evening, DATEDIFF(n, START_TUE,
LUNCH_OUT_TUE) + DATEDIFF(n, LUNCH_BACK_TUE, END_TUE) AS Tot_TUEday, START_Wed, LUNCH_OUT_Wed, LUNCH_BACK_Wed,
END_Wed, DATEDIFF(n, START_Wed, LUNCH_OUT_Wed) AS Wed_morning, DATEDIFF(n, LUNCH_BACK_Wed, END_Wed)
AS Wed_Evening, DATEDIFF(n, START_Wed, LUNCH_OUT_Wed) + DATEDIFF(n, LUNCH_BACK_Wed, END_Wed) AS Tot_Wedday,
START_Thu, LUNCH_OUT_Thu, LUNCH_BACK_Thu, END_Thu, DATEDIFF(n, START_Thu, LUNCH_OUT_Thu) AS Thu_morning, DATEDIFF(n,
LUNCH_BACK_Thu, END_Thu) AS Thu_Evening, DATEDIFF(n, START_Thu, LUNCH_OUT_Thu) + DATEDIFF(n, LUNCH_BACK_Thu, END_Thu)
AS Tot_Thuday, START_Fri, LUNCH_OUT_Fri, LUNCH_BACK_Fri, END_Fri, DATEDIFF(n, START_Fri, LUNCH_OUT_Fri) AS Fri_morning,
DATEDIFF(n, LUNCH_BACK_Fri, END_Fri) AS Fri_Evening, DATEDIFF(n, START_Fri, LUNCH_OUT_Fri) + DATEDIFF(n, LUNCH_BACK_Fri,
END_Fri) AS Tot_Friday, DATEDIFF(n, START_MON, LUNCH_OUT_MON) + DATEDIFF(n, LUNCH_BACK_MON, END_MON) + DATEDIFF(n,
START_TUE, LUNCH_OUT_TUE) + DATEDIFF(n, LUNCH_BACK_TUE, END_TUE) + DATEDIFF(n, START_Wed, LUNCH_OUT_Wed)
+ DATEDIFF(n, LUNCH_BACK_Wed, END_Wed) + DATEDIFF(n, START_Thu, LUNCH_OUT_Thu) + DATEDIFF(n, LUNCH_BACK_Thu, END_Thu)
+ DATEDIFF(n, START_Fri, LUNCH_OUT_Fri) + DATEDIFF(n, LUNCH_BACK_Fri, END_Fri) AS [total Week]
FROM          dbo.EMPLOYEE_TIMESHEET) a
``````
0

Author Commented:
Hi Brandon
Fantastic. Thanks a million.
Is the code I have written ok or could it be written simpler?
0

Author Commented:
Thanks again
0

Commented:
Ask as a related Q? and I'll see it right away.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.