Solved

Convert minutes to HH,MM

Posted on 2008-10-15
11
268 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
Free eBook: Backup on AWS

Everything you need to know about backup and disaster recovery with AWS, for FREE!

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

Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
unable to reinstall SCVMM 2012 R2 - SQL errors. 5 44
Need SSIS project 2 31
SSRS Page Header from Group Data 2 30
Data encryption options between SQL DBs 3 33
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…
International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.

697 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