Solved

Convert minutes to HH,MM

Posted on 2008-10-15
11
267 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
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
Simplifying Server Workload Migrations

This use case outlines the migration challenges that organizations face and how the Acronis AnyData Engine supports physical-to-physical (P2P), physical-to-virtual (P2V), virtual to physical (V2P), and cross-virtual (V2V) migration scenarios to address these challenges.

 
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

VMware Disaster Recovery and Data Protection

In this expert guide, you’ll learn about the components of a Modern Data Center. You will use cases for the value-added capabilities of Veeam®, including combining backup and replication for VMware disaster recovery and using replication for data center migration.

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…
I have a large data set and a SSIS package. How can I load this file in multi threading?
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

856 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