Solved

Convert minutes to HH,MM

Posted on 2008-10-15
11
253 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
Comment Utility
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
Comment Utility
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
Comment Utility
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
 
LVL 39

Expert Comment

by:BrandonGalderisi
Comment Utility
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
Comment Utility
And I used [total week] because you stated "result of time worked Total Week is  in Minutes"
0
Zoho SalesIQ

Hassle-free live chat software re-imagined for business growth. 2 users, always free.

 

Author Comment

by:Camnoc
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
Thanks again
0
 
LVL 39

Expert Comment

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

Featured Post

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

When you hear the word proxy, you may become apprehensive. This article will help you to understand Proxy and when it is useful. Let's talk Proxy for SQL Server. (Not in terms of Internet access.) Typically, you'll run into this type of problem w…
Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
Via a live example, show how to setup several different housekeeping processes for a SQL Server.
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.

728 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

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now