Solved

DATEDIFF    DATEADD

Posted on 2008-10-07
33
972 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
Comment Utility
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
Comment Utility
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
Comment Utility
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
 
LVL 92

Assisted Solution

by:Patrick Matthews
Patrick Matthews earned 80 total points
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
No need to use a UDF at all.  It just over-complicates things
0
 
LVL 39

Accepted Solution

by:
BrandonGalderisi earned 310 total points
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Maximize Your Threat Intelligence Reporting

Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

 

Author Comment

by:Camnoc
Comment Utility
Hello
0
 
LVL 39

Expert Comment

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

Author Comment

by:Camnoc
Comment Utility
No Problem

Thank You

0
 
LVL 4

Assisted Solution

by:rehand
rehand earned 70 total points
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
Take as long as you need
0
 
LVL 39

Expert Comment

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

Author Comment

by:Camnoc
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
Hi Brandon
Getting error
The Query Designer does not support the @referencedate SQL construct.
0
 
LVL 39

Expert Comment

by:BrandonGalderisi
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
Thank you all very much especially  Brandon
0

Featured Post

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.

Join & Write a Comment

Entity Framework is a powerful tool to help you interact with the DataBase but still doesn't help much when we have a Stored Procedure that returns more than one resultset. The solution takes some of out-of-the-box thinking; read on!
Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

772 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

9 Experts available now in Live!

Get 1:1 Help Now