• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1002
  • Last Modified:

DATEDIFF DATEADD

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
Camnoc
Asked:
Camnoc
  • 13
  • 11
  • 4
  • +2
12 Solutions
 
Patrick MatthewsCommented:
SELECT     time_in, time_out1, LEFT(CONVERT(varchar, time_out1 - time_in, 108), 5) AS result
FROM         dbo.timesheet1
0
 
Kevin CrossChief Technology OfficerCommented:
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
 
Patrick MatthewsCommented:
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
Never miss a deadline with monday.com

The revolutionary project management tool is here!   Plan visually with a single glance and make sure your projects get done.

 
Patrick MatthewsCommented:
Camnoc,

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

Patrick
0
 
Kevin CrossChief Technology OfficerCommented:
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
 
CamnocAuthor Commented:
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
 
Patrick MatthewsCommented:
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
 
CamnocAuthor Commented:
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
 
rehandCommented:
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
 
BrandonGalderisiCommented:
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
 
rehandCommented:
I love it Brandon. Take Brandons solution and wrap it in a UDF and you are good to go.
0
 
BrandonGalderisiCommented:
No need to use a UDF at all.  It just over-complicates things
0
 
BrandonGalderisiCommented:
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
 
CamnocAuthor Commented:
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
 
BrandonGalderisiCommented:
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
 
CamnocAuthor Commented:
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
 
CamnocAuthor Commented:
Hello
0
 
BrandonGalderisiCommented:
I have this flagged to get back to.  I just haven't gotten a chance yet.  Be patient please.
0
 
CamnocAuthor Commented:
No Problem

Thank You

0
 
rehandCommented:
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
 
CamnocAuthor Commented:
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
 
BrandonGalderisiCommented:
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
 
CamnocAuthor Commented:
Take as long as you need
0
 
BrandonGalderisiCommented:
For a reference point, what is the earliest data point in your system?
0
 
CamnocAuthor Commented:
Hi Brandon
I presume you mean earliest date This could be Mon 6th Oct..
0
 
BrandonGalderisiCommented:
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
 
CamnocAuthor Commented:
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
 
BrandonGalderisiCommented:
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
 
CamnocAuthor Commented:
Hi Brandon
Getting error
The Query Designer does not support the @referencedate SQL construct.
0
 
BrandonGalderisiCommented:
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
 
CamnocAuthor Commented:
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
 
BrandonGalderisiCommented:
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
 
CamnocAuthor Commented:
Thank you all very much especially  Brandon
0

Featured Post

Get expert help—faster!

Need expert help—fast? Use the Help Bell for personalized assistance getting answers to your important questions.

  • 13
  • 11
  • 4
  • +2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now