Solved

Help time difference between two dates

Posted on 2013-01-25
4
247 Views
Last Modified: 2013-01-26
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
0
Comment
Question by:lulu50
  • 2
4 Comments
 
LVL 69

Accepted Solution

by:
ScottPletcher earned 500 total points
Comment Utility
DATEDIFF(MINUTE, PunchInDate, PunchOutDate) / 60.0 AS Hours_In
0
 
LVL 73

Expert Comment

by:sdstuber
Comment Utility
datediff(hh,punchindate,punchoutdate) will give you whole hours
0
 
LVL 73

Expert Comment

by:sdstuber
Comment Utility
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
0
 

Author Closing Comment

by:lulu50
Comment Utility
Thank you
0

Featured Post

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.

Join & Write a Comment

In this article I will describe the Copy Database Wizard method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties

763 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

6 Experts available now in Live!

Get 1:1 Help Now