x
Solved

Posted on 2008-10-07
Medium Priority
1,005 Views
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
``````
0
Question by:Camnoc
• 13
• 11
• 4
• +2

LVL 93

Assisted Solution

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 61

Assisted Solution

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
``````
0

LVL 93

Assisted Solution

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

LVL 93

Assisted Solution

Patrick Matthews earned 320 total points
ID: 22658584
Camnoc,

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

Patrick
0

LVL 61

Assisted Solution

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

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

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

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

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

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'

0

LVL 4

Expert Comment

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

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

LVL 39

Accepted Solution

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')
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
``````
0

Author Comment

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

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

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

ID: 22672089
Hello
0

LVL 39

Expert Comment

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

Author Comment

ID: 22672586
No Problem

Thank You

0

LVL 4

Assisted Solution

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)
``````
0

Author Comment

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

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

ID: 22672958
Take as long as you need
0

LVL 39

Expert Comment

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

Author Comment

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

LVL 39

Assisted Solution

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
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
``````
0

Author Comment

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

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

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

LVL 39

Expert Comment

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

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

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

ID: 31503788
Thank you all very much especially  Brandon
0

## Featured Post

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.