lulu50
asked on
Help time difference between two dates
Hi,
I have a table that has two dates
I need to find out the hour of the employees when they punch in and punch out
MONTHLY
WEEKLY
DAILY
Id storeID employeeID PunchInDate PunchOutDate
1 9 13000505 2013-01-01 03:07:08.000 2013-01-01 06:32:32.000
2 9 13000505 2013-01-02 02:06:05.000 2013-01-02 05:31:31.000
3 9 13000505 2013-01-03 02:07:06.000 2013-01-03 05:31:32.000
4 9 13000505 2013-01-04 04:10:11.000 2013-01-04 06:39:40.000
5 9 6000247 2013-01-24 09:12:41.000 NULL
6 9 10000000 2013-01-24 09:12:41.000 2013-01-24 10:12:41.000
7 1 30 2013-01-25 00:00:00.000 2013-01-25 00:00:00.000
8 1 39 2013-01-25 00:00:00.000 NULL
9 1 54 2013-01-25 00:00:00.000 NULL
10 1 6000125 2013-01-25 04:10:11.000 2013-01-25 06:39:40.000
HOW CAN I CALCULATE THE HOUR THAT THE EMPLOYEE PUNCH IN AND PUNCH OUT
I have a table that has two dates
I need to find out the hour of the employees when they punch in and punch out
MONTHLY
WEEKLY
DAILY
Id storeID employeeID PunchInDate PunchOutDate
1 9 13000505 2013-01-01 03:07:08.000 2013-01-01 06:32:32.000
2 9 13000505 2013-01-02 02:06:05.000 2013-01-02 05:31:31.000
3 9 13000505 2013-01-03 02:07:06.000 2013-01-03 05:31:32.000
4 9 13000505 2013-01-04 04:10:11.000 2013-01-04 06:39:40.000
5 9 6000247 2013-01-24 09:12:41.000 NULL
6 9 10000000 2013-01-24 09:12:41.000 2013-01-24 10:12:41.000
7 1 30 2013-01-25 00:00:00.000 2013-01-25 00:00:00.000
8 1 39 2013-01-25 00:00:00.000 NULL
9 1 54 2013-01-25 00:00:00.000 NULL
10 1 6000125 2013-01-25 04:10:11.000 2013-01-25 06:39:40.000
HOW CAN I CALCULATE THE HOUR THAT THE EMPLOYEE PUNCH IN AND PUNCH OUT
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
datediff(hh,punchindate,pu nchoutdate ) will give you whole hours
to get hours/minutes/seconds
select totalseconds/ 3600 hours,
(totalseconds% 3600)/60 minutes,
totalseconds% 60 seconds
from
(
select datediff(ss, '2013-01-01 03:07:08.000', '2013-01-01 06:32:32.000')as totalseconds
) as x
select totalseconds/ 3600 hours,
(totalseconds% 3600)/60 minutes,
totalseconds% 60 seconds
from
(
select datediff(ss, '2013-01-01 03:07:08.000', '2013-01-01 06:32:32.000')as totalseconds
) as x
ASKER
Thank you