I want Hours,Min, second difference from two datetime

Question

I am developing Time management system for employees.

I want the duration how much duration employee come late , or he went early.

i have following structure.

**Attendace**  
AutoId --uniqueidentifier             
EMployeeId --uniqueidentifier
Date   --datetime
InTime -- varchar(50)
OutTime -- varchar(50)
ActualInTime--datetime
ActualOutTime--datetime

I want Late Coming Report ( i.e. who came late in morning after ActualInTime and how much duration in hh:mm:ss ) and also want early going(i.e who went early in the evening before ActualOutTime in duration in format hh:mm:ss )

So can you please help me..???

Solution 1

this code might help you...

DECLARE @First datetime
DECLARE @Second datetime
SET @First = '04/02/2008 05:23:22'
SET @Second = getdate()

SELECT DATEDIFF(day,@First,@Second)*24 as TotalHours,
DATEDIFF(day,@First,@Second)*24*60 as TotalMinutes,
DATEDIFF(day,@First,@Second)*24*60*60 as TotalSeconds

Solution 2

You can do it in a very simple way:

declare  @date1 datetime, @date2 datetime
set @date1=DATEADD(s,-638,getdate())
set @date2=GETDATE()

select convert(char(8),dateadd(s,datediff(s,@date1,@date2),'1900-1-1'),8)

... the result is 00:10:38 (638s = 600s + 38s = 10 minutes and 38 seconds)

Another example:

select distinct convert(char(8),dateadd(s,datediff(s, CRDATE , GETDATE() ),'1900-1-1'),8) from sysobjects order by 1

It will works until the difference of 86399 seconds (23:59:59):

select convert(char(8),dateadd(s,datediff(s
    , DATEADD(s,-86399,GETDATE())
    , GETDATE()
),'1900-1-1'),8)

... after that it will return to zero:

select convert(char(8),dateadd(s,datediff(s
    , DATEADD(s,-86400,GETDATE())
    , GETDATE()
),'1900-1-1'),8)

Solution 3

I came across an easier way of solving this issue.

First, a quick example of turning a "number of seconds" into the "hh:mm:ss" format.

DECLARE @NumberOfSeconds int
SET @NumberOfSeconds = 3843     --  1 hour, 4 minutes, 3 seconds

SELECT @NumberOfSeconds AS 'seconds',
   CONVERT(varchar, DATEADD(second, @NumberOfSeconds, 0), 108) AS 'hh:mm:ss'

This will give us this output:

And we can easily take this a step further, calculate the number of seconds between two datetimes, and display it in hh:mm:ss format:

DECLARE 
    @NumberOfSeconds int,
    @StartTime datetime = '2017-09-14 14:16:11',
    @EndTime datetime = '2017-09-14 14:23:13'

SET @NumberOfSeconds = DATEDIFF(second, @StartTime, @EndTime)

SELECT @NumberOfSeconds AS 'seconds',
     CONVERT(varchar, DATEADD(second, @NumberOfSeconds, 0), 108) AS 'hh:mm:ss'

Which gives us this output:

Simple, hey ?

(And yes, you can simplify it further by putting the DATEDIFF directly into the DATEADD function.)

Solution 4

Because they are the same day (you don't have to worry about number of hours >24), you can just use a combination of DATEDIFF(second,time1,time2) and DATEADD(second,0,) to get a datetime value.

To format to hh:nn:ss, use convert(char(8),answer,8) but this is something better done by the reporting front end against the datetime result.

-- Late report
select *, dateadd(s,0,datediff(s,intime,actualintime)) late_by
from attendance
where intime < actualintime

Solution 5

How about using CAST.

,CAST (Table1.DateTimeLatest-Table1.DateTimeFirst as time) as [Elapsed Time]

The raw result from SSMS from an apparatus table: SQL Return shows out to nanoseconds in this Data.

For the report, as pasted in formatted Excel sheet: Formatted result column as hh:mm:ss.

View more solutions

Comments

[Mahaveer Jangid]

SELECT DATEDIFF(hour,'2020-06-10 07:16:11.823',GETUTCDATE()) as TotalHours SELECT DATEDIFF(minute,'2020-06-10 07:16:11.823',GETUTCDATE()) as TotalMinutes