?
Solved

DATEDIFF    DATEADD

Posted on 2008-10-07
33
Medium Priority
?
991 Views
Last Modified: 2010-04-21
hI
I am currently trying to develop a simple time log for people on flexitime.
I am using Dreamweaver asp vb to develop forms working to SQL SERVER.
My problem is i am using sql server to do the calculations ie Time in , Time out . Using
the attached code I get the result of the time difference in minutes, however I need the result in Hours and minutes.
Is there a function to add hours and minutes giving result in hh.mm
Would javascript or asp be a better solution to carry out calculations
SELECT     time_in, time_out1, DATEDIFF(minute, time_in, time_out1) AS result
FROM         dbo.timesheet1

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
  • 13
  • 11
  • 4
  • +2
33 Comments
 
LVL 93

Assisted Solution

by:Patrick Matthews
Patrick Matthews earned 320 total points
ID: 22658538
SELECT     time_in, time_out1, LEFT(CONVERT(varchar, time_out1 - time_in, 108), 5) AS result
FROM         dbo.timesheet1
0
 
LVL 60

Assisted Solution

by:Kevin Cross
Kevin Cross earned 160 total points
ID: 22658572
Can argue both ways on placement of code, so that is up to you.  Here is one way you would do this in SQL.
SELECT *, Cast(result/60 AS varchar(2)) + '.' + Right('00' + Cast(result % 60 AS varchar(2)), 2)
FROM (
SELECT     time_in, time_out1, DATEDIFF(minute, time_in, time_out1) AS result
FROM dbo.timesheet1
) AS derived

Open in new window

0
 
LVL 93

Assisted Solution

by:Patrick Matthews
Patrick Matthews earned 320 total points
ID: 22658576
Camnoc,

Please note that that will not work if there if you have an interval of >= 24 hours.  If there is any possibility
of that...

SELECT CONVERT(varchar, DATEDIFF(hh, time_in, time_out1)) + ':' + RIGHT('0' + CONVERT(varchar,
    DATEDIFF(mi, time_in, time_out1) % 60), 2) AS result
FROM dbo.timesheet1

Regards,

Patrick
0
Visualize your virtual and backup environments

Create well-organized and polished visualizations of your virtual and backup environments when planning VMware vSphere, Microsoft Hyper-V or Veeam deployments. It helps you to gain better visibility and valuable business insights.

 
LVL 93

Assisted Solution

by:Patrick Matthews
Patrick Matthews earned 320 total points
ID: 22658584
Camnoc,

By "that", I was referring to my first post.

Patrick
0
 
LVL 60

Assisted Solution

by:Kevin Cross
Kevin Cross earned 160 total points
ID: 22658588
Or go ahead and use the really easy convert function. :) Just then have to replace ':' with '.'.

SELECT     time_in, time_out1, LEFT(REPLACE(CONVERT(varchar, time_out1 - time_in, 108),':','.'), 5) AS result
FROM         dbo.timesheet1
0
 

Author Comment

by:Camnoc
ID: 22659207
Thanks guys
All solutions work welll.  Al the in/out times would be within 24 hours however I will need to total hours for end of week, How do I get tolal hours ie   time1+ time2+time 3
0
 
LVL 93

Assisted Solution

by:Patrick Matthews
Patrick Matthews earned 320 total points
ID: 22659311
Camnoc said:
>>How do I get tolal hours ie   time1+ time2+time 3

Do you have columns for each day's in/out times?  If so, that is a poor design.  Rather, I would start with a
schema like this, and expand as needed:

tblEmployees
--------------------------------------------------------------
EmpID (PK)
EmpFName
EmpLName


tblHrTypes (ie, regular, holiday, sick, vacation, etc)
--------------------------------------------------------------
HrTypeID (PK)
HrTypeDescr

TimeOut
0
 

Author Comment

by:Camnoc
ID: 22659570
Thanks matthewspatrick.

I was thinking of using Employee tbl  and Hours Worked tbl using colums  for Sart_time, Tme_out,
Return_time, Finish-time. Iwill need a columns to  show time_remaining ,from( 35.00 hrs) time_over  (35.00 hrs). I will also need to be able to total hours worked.
My knowledge of SQL is limited . Up to recently I have been using Access, SQL Enterprise Manager is what I now use. I have been using Google for help unfortunately most help id s for advanced users
0
 
LVL 4

Assisted Solution

by:rehand
rehand earned 280 total points
ID: 22661647
You can create a UDF to do this and use it as you please.

CREATE FUNCTION getHoursWorked (@DateIN DATETIME, @DateOut DATETIME)
RETURNS VARCHAR(10)
AS
      BEGIN
            DECLARE @ReturnValue VARCHAR(10)
            SET @ReturnValue = CAST(DATEDIFF(minute, @DateIN, @DateOut)/60 AS VARCHAR) + ':' + CAST(DATEDIFF(minute, @DateIN, @DateOut)%60 AS VARCHAR)
            RETURN @ReturnValue
      END

SELECT dbo.getHoursWorked('1/1/2008 08:20', '1/1/2008 10:55')
0
 
LVL 39

Assisted Solution

by:BrandonGalderisi
BrandonGalderisi earned 1240 total points
ID: 22662036
Here you go.


declare    @Datein  datetime
          ,@DateOut datetime
set @datein = '1/1/2008 12am'
set @dateout = '1/1/2008 2pm'
         

select convert(char(5),dateadd(n,datediff(n,@datein,@dateout),0),108)




0
 
LVL 4

Expert Comment

by:rehand
ID: 22662100
I love it Brandon. Take Brandons solution and wrap it in a UDF and you are good to go.
0
 
LVL 39

Expert Comment

by:BrandonGalderisi
ID: 22662216
No need to use a UDF at all.  It just over-complicates things
0
 
LVL 39

Accepted Solution

by:
BrandonGalderisi earned 1240 total points
ID: 22662325
I was thinking of using Employee tbl  and Hours Worked tbl using colums  for Sart_time, Tme_out,
Return_time, Finish-time. Iwill need a columns to  show time_remaining ,from( 35.00 hrs) time_over  (35.00 hrs). I will also need to be able to total hours worked.
My knowledge of SQL is limited . Up to recently I have been using Access, SQL Enterprise Manager is what I now use. I have been using Google for help unfortunately most help id s for advanced users


For final display purposes, you want the sum of hours worked in the hh:nn format, but in all reality, you should have a time in/out table.  If someone clocks in/out two times in a day, so be it.  You don't want 4 columns to represent that.  Because what if they have to clock in/out 3 times?



Here's a quick example to show you:




set nocount on
 
create table #EmployeeTime    
     (empid         int not null
     ,Time_in       datetime not null
     ,time_out      datetime null
     )
insert into #employeeTime values(1, '10/7/2008 1pm', '10/7/2008 3pm')
insert into #employeeTime values(1, '10/7/2008 8am', '10/7/2008 12:30pm')
insert into #employeeTime values(1, '10/8/2008 8am', '10/8/2008 11pm')
select empid,tDate,sum(tTime) tMinutes, convert(char(5),dateadd(n,sum(tTime),0),108) tHoursMinutes
from
     (select empID, convert(datetime, convert(varchar(10), time_in, 101),101) tdate, datediff(n,time_in,time_out) ttime
     from #employeetime) a
group by empid,tDate
 
go
drop table #employeeTime

Open in new window

0
 

Author Comment

by:Camnoc
ID: 22664100
Hi BrandonGalderisi
Thank you
This is great. One problem.  I will still need to show a Total Hours and Minutes worked  for week endiing Sunday. I will also need to show if possible Time Remaining and time over if a working week is 35.00 hours.
0
 
LVL 39

Assisted Solution

by:BrandonGalderisi
BrandonGalderisi earned 1240 total points
ID: 22664143
This was just a sample for starters.  Do you have a table or reference of pay periods?  I need to know how to group days into weeks.  Using datepart(w,DateValue) isn't a good option because if a pay period runs from Dec-31 to Jan 6, you have two different values.

If you don't, providing a single reference date (such as 05-Oct-2008) as a valid starting point would work.  But that date should less than any existing date.  Once I have one of those, aggregating the data is easy.
0
 

Author Comment

by:Camnoc
ID: 22664515
Hi BrandonGalderis

This log will record weekly time worked per day for 52 weeks . The working week will be 35.00 hours
The week ending is Sunday of each week. The year starts on the first Monday of the Year but I dont think this is important ..
The important thing is to record a weekly record of attendance each day from Monday to Friday of each week,and to carry over time >35.00 hours. One problem I see is the person entering the time will only enter the time as in 09.00 or 14.00 how will the date be entered as your table?
0
 

Author Comment

by:Camnoc
ID: 22672089
Hello
0
 
LVL 39

Expert Comment

by:BrandonGalderisi
ID: 22672438
I have this flagged to get back to.  I just haven't gotten a chance yet.  Be patient please.
0
 

Author Comment

by:Camnoc
ID: 22672586
No Problem

Thank You

0
 
LVL 4

Assisted Solution

by:rehand
rehand earned 280 total points
ID: 22672645
I don't have much time so right now, but I wanted to give you something. Please not that this make assumptions on server settings.
I will try to get back to this tonight.

DECLARE @TimeCard TABLE (emp_id int, time_in datetime, time_out datetime)
 
INSERT INTO @TimeCard VALUES (1, '10/1/2008 08:00', '10/1/2008 17:00')
INSERT INTO @TimeCard VALUES (1, '10/2/2008 08:00', '10/2/2008 17:00')
INSERT INTO @TimeCard VALUES (1, '10/3/2008 08:00', '10/3/2008 17:00')
INSERT INTO @TimeCard VALUES (1, '10/4/2008 08:00', '10/4/2008 17:00')
INSERT INTO @TimeCard VALUES (1, '10/5/2008 08:00', '10/5/2008 17:00')
INSERT INTO @TimeCard VALUES (1, '10/6/2008 08:00', '10/6/2008 17:00')
INSERT INTO @TimeCard VALUES (1, '10/7/2008 08:00', '10/7/2008 17:00')
 
 
SELECT 
	emp_id,
	DatePart(wk, time_in) as WeekOfYear,
	DATEDIFF(minute, time_in, time_out) AS MinutesWorked
FROM 
	@TimeCard
 
SELECT 
	emp_id,
	DatePart(wk, time_in) as WeekOfYear,
	SUM(DATEDIFF(minute, time_in, time_out)) AS MinutesWorked,
	CASE 
		WHEN SUM(DATEDIFF(minute, time_in, time_out)) <= (35*60) THEN 0
		ELSE SUM(DATEDIFF(minute, time_in, time_out)) - (35*60)
	END AS MinutesCarriedOver
FROM 
	@TimeCard
GROUP BY
	emp_id,
	DatePart(wk, time_in)

Open in new window

0
 

Author Comment

by:Camnoc
ID: 22672741
Thank you very much.
Looking forward to hearing from you.
I will need your help to insert this into SQL, unfortunately SQL is new to me I am learning though.
0
 
LVL 39

Expert Comment

by:BrandonGalderisi
ID: 22672833
The datepart(wk method won't work for the reason I stated above.  12/31 and 1/1 (if a monday/tuesday) will have different values because they are all relevant to the same year.  I know exactly how to solve this, I'll get to it in a couple of hours.
0
 

Author Comment

by:Camnoc
ID: 22672958
Take as long as you need
0
 
LVL 39

Expert Comment

by:BrandonGalderisi
ID: 22674420
For a reference point, what is the earliest data point in your system?
0
 

Author Comment

by:Camnoc
ID: 22674598
Hi Brandon
I presume you mean earliest date This could be Mon 6th Oct..
0
 
LVL 39

Assisted Solution

by:BrandonGalderisi
BrandonGalderisi earned 1240 total points
ID: 22674735
Try this and let me know if this gives you what you need:


set nocount on
set datefirst 1
declare @ReferenceDate datetime
set @ReferenceDate = convert(datetime, '10/06/2008',101)
 
create table #EmployeeTime    
     (empid         int not null
     ,Time_in       datetime not null
     ,time_out      datetime null
     )
insert into #employeeTime values(1, '10/6/2008 1pm', '10/6/2008 3pm')
insert into #employeeTime values(1, '10/7/2008 1pm', '10/7/2008 3pm')
insert into #employeeTime values(1, '10/8/2008 1pm', '10/8/2008 3pm')
 
insert into #employeeTime values(1, '10/9/2008 1pm', '10/9/2008 3pm')
insert into #employeeTime values(1, '10/10/2008 1pm', '10/10/2008 3pm')
insert into #employeeTime values(1, '10/11/2008 1pm', '10/11/2008 3pm')
 
 
insert into #employeeTime values(1, '10/12/2008 1pm', '10/12/2008 3pm')
insert into #employeeTime values(1, '10/13/2008 8am', '10/13/2008 12:30pm')
insert into #employeeTime values(1, '10/25/2008 8am', '10/25/2008 11pm')
 
select empid,weekdiff,tdate,sum(tTime) tMinutes
, convert(char(5),dateadd(n,sum(tTime),0),108) tHoursMinutes
from
     (select empID, convert(datetime, convert(varchar(10), time_in, 101),101) tdate, datediff(n,time_in,time_out) ttime, datepart(dw,time_in) dw,datediff(d,@referencedate,time_in)/7 as WeekDiff
     from #employeetime) a
group by empid,tdate,weekdiff
 
go
drop table #employeeTime

Open in new window

0
 

Author Comment

by:Camnoc
ID: 22674931
Thank you so much.
Its 2am here so I wont try out until tomorrow morning


Silly Question

Where does
set nocount on
set datefirst 1
declare @ReferenceDate datetime
set @ReferenceDate = convert(datetime, '10/06/2008',101)
0
 
LVL 39

Expert Comment

by:BrandonGalderisi
ID: 22675499
set nocount on

supresses
(x rows affected) messages

set datefirst 1
ensures that for the active query, that monday is treated as the first day of the week.

declare @ReferenceDate datetime
set @ReferenceDate = convert(datetime, '10/06/2008',101)
I am using @ReferenceDate as the beginning of week 0.  From there, each 7 day period is aggregated together.  So Mon-Sun, Mon-Sun, Mon-Sun will be referenced internally as week 0,1,2.  If you would prefer the display to be 1,2,3 then change the reference date to sep-9-2008.
0
 

Author Comment

by:Camnoc
ID: 22678473
Hi Brandon
Getting error
The Query Designer does not support the @referencedate SQL construct.
0
 
LVL 39

Expert Comment

by:BrandonGalderisi
ID: 22679091
You need to put this in a new query window, not the query designer.  Also, if you are planning on putting this in a view, you can pull out the variable @referencedate and replace it with:

convert(datetime, '10/06/2008',101)
0
 

Author Comment

by:Camnoc
ID: 22680008
This is great .
Thank you very much
I am using SQL 2000.Enterprise Manager.
I just need to think of a way to input the time via my web page that will include the the date, any ideas

Thanks again
0
 
LVL 39

Expert Comment

by:BrandonGalderisi
ID: 22680625
A calendar control and a date box.  I'm sure there are some for classic asp that you can find.  But you don't want to leave it to the user to enter it free text (IMO).
0
 

Author Closing Comment

by:Camnoc
ID: 31503788
Thank you all very much especially  Brandon
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

What if you have to shut down the entire Citrix infrastructure for hardware maintenance, software upgrades or "the unknown"? I developed this plan for "the unknown" and hope that it helps you as well. This article explains how to properly shut down …
Windocks is an independent port of Docker's open source to Windows.   This article introduces the use of SQL Server in containers, with integrated support of SQL Server database cloning.
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

719 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