Solved

DATEDIFF    DATEADD

Posted on 2008-10-07
33
977 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
  • 13
  • 11
  • 4
  • +2
33 Comments
 
LVL 92

Assisted Solution

by:Patrick Matthews
Patrick Matthews earned 80 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 59

Assisted Solution

by:Kevin Cross
Kevin Cross earned 40 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 92

Assisted Solution

by:Patrick Matthews
Patrick Matthews earned 80 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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
LVL 92

Assisted Solution

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

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

Patrick
0
 
LVL 59

Assisted Solution

by:Kevin Cross
Kevin Cross earned 40 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 92

Assisted Solution

by:Patrick Matthews
Patrick Matthews earned 80 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 70 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 310 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 310 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 310 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 70 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 310 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

Optimizing Cloud Backup for Low Bandwidth

With cloud storage prices going down a growing number of SMBs start to use it for backup storage. Unfortunately, business data volume rarely fits the average Internet speed. This article provides an overview of main Internet speed challenges and reveals backup best practices.

Question has a verified solution.

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

Suggested Solutions

Read about achieving the basic levels of HRIS security in the workplace.
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…
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.
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties

773 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