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..???

Answer 1

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.)