Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

Convert minutes to HH,MM

Posted on 2008-10-15
11
Medium Priority
?
281 Views
Last Modified: 2010-04-21
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

Open in new window

0
Comment
Question by:Camnoc
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
11 Comments
 
LVL 37

Expert Comment

by:momi_sabag
ID: 22721341
if @mins is your minutes then
 

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

will give you what you want
0
 
LVL 31

Expert Comment

by:James Murrell
ID: 22721353
Assuming that Mints is the field holding the number of minutes

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

Expert Comment

by:wellso
ID: 22721411
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
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
LVL 39

Expert Comment

by:BrandonGalderisi
ID: 22721447
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

Open in new window

0
 
LVL 39

Expert Comment

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

Author Comment

by:Camnoc
ID: 22722568
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

Open in new window

0
 
LVL 39

Expert Comment

by:BrandonGalderisi
ID: 22722639
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

Open in new window

0
 

Author Comment

by:Camnoc
ID: 22722749
Hi Brandon
Fantastic. Thanks a million.
Is the code I have written ok or could it be written simpler?
0
 
LVL 39

Accepted Solution

by:
BrandonGalderisi earned 2000 total points
ID: 22722776
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
 

Author Closing Comment

by:Camnoc
ID: 31506309
Thanks again
0
 
LVL 39

Expert Comment

by:BrandonGalderisi
ID: 22722808
Ask as a related Q? and I'll see it right away.
0

Featured Post

Enroll in October's Free Course of the Month

Do you work with and analyze data? Enroll in October's Course of the Month for 7+ hours of SQL training, allowing you to quickly and efficiently store or retrieve data. It's free for Premium Members, Team Accounts, and Qualified Experts!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
In part one, we reviewed the prerequisites required for installing SQL Server vNext. In this part we will explore how to install Microsoft's SQL Server on Ubuntu 16.04.
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed
Via a live example, show how to shrink a transaction log file down to a reasonable size.

610 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question