I want Hours,Min, second difference from two datetime

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开发者_C百科 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..???

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

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)

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

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

well, yes, you need to use DATEDIFF, and yes, all that posted above works, but, if you want to show 07:07:07 instead of 7:7:7, you have to do something like this:

Declare @starttime datetime, @endtime datetime, @seconds int, @minutes int, @hours int

Set @starttime ='2013-10-01 05:05:17'
Set @endtime = '2013-10-01 23:10:18'

set @hours = DateDiff(hour, @starttime, @endtime) 
set @minutes = DateDiff(minute, @starttime, @endtime);
set @seconds = DateDiff(second, @starttime, @endtime);

select case when DateDiff(minute, @starttime, @endtime) > 60 
        then CASE WHEN @hours >= 10 THEN cast(@hours as varchar(3))
                 ELSE '0' + cast(@hours as varchar(3)) END +':' + 
             CASE WHEN @minutes - (@hours * 60) >= 10 THEN
                 cast((@minutes - (@hours * 60)) as varchar(3))
                 ELSE '0' +cast((@minutes - (@hours * 60)) as varchar(3)) END
             + CASE WHEN (@seconds - (@minutes *60)) >= 10 THEN
                 +':' + cast(@seconds - (@minutes *60) as varchar(10)) 
                 ELSE ':0' + cast(@seconds - (@minutes *60) as varchar(10)) END
        ELSE '0' + cast(@minutes as varchar(3)) +':' + cast(@seconds as varchar(10)) 
end

It may not look very nice, but it gave me what i wanted.

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.

SELECT id, pickupdateandtime, GETDATE() AS CurrentTime, 
((DATEDIFF(day,GETDATE(),pickupdateandtime)) - 1) AS Days , 
convert(char(8),dateadd(s,datediff(s,GETDATE(),pickupdateandtime),'1900-1-
1'),8) AS 'Hours & Mins' FROM orders

Here's what worked for me. Thank you @lynx_74.

https://i.stack.imgur.com/hOmyJ.png

Create a stored procedure to do the work and then just call the procedure passing your start and end dates.

CREATE PROCEDURE [dbo].[GetOperationDuration]  
   @DurationStart DATETIME, @DurationEnd DATETIME,
   @Duration VARCHAR(100) OUTPUT
AS  
BEGIN  
DECLARE @years INT, @months INT, @days INT,
   @hours INT, @minutes INT, @seconds INT, @milliseconds INT;

-- DOES NOT ACCOUNT FOR LEAP YEARS
SELECT @years = DATEDIFF(yy, @DurationStart, @DurationEnd)
IF DATEADD(yy, -@years, @DurationEnd) < @DurationStart 
SELECT @years = @years-1
SET @DurationEnd = DATEADD(yy, -@years, @DurationEnd)

SELECT @months = DATEDIFF(mm, @DurationStart, @DurationEnd)
IF DATEADD(mm, -@months, @DurationEnd) < @DurationStart 
SELECT @months=@months-1
SET @DurationEnd= DATEADD(mm, -@months, @DurationEnd)

SELECT @days=DATEDIFF(dd, @DurationStart, @DurationEnd)
IF DATEADD(dd, -@days, @DurationEnd) < @DurationStart 
SELECT @days=@days-1
SET @DurationEnd= DATEADD(dd, -@days, @DurationEnd)

SELECT @hours=DATEDIFF(hh, @DurationStart, @DurationEnd)
IF DATEADD(hh, -@hours, @DurationEnd) < @DurationStart 
SELECT @hours=@hours-1
SET @DurationEnd= DATEADD(hh, -@hours, @DurationEnd)

SELECT @minutes=DATEDIFF(mi, @DurationStart, @DurationEnd)
IF DATEADD(mi, -@minutes, @DurationEnd) < @DurationStart 
SELECT @minutes=@minutes-1
SET @DurationEnd= DATEADD(mi, -@minutes, @DurationEnd)

SELECT @seconds=DATEDIFF(s, @DurationStart, @DurationEnd)
IF DATEADD(s, -@seconds, @DurationEnd) < @DurationStart 
SELECT @seconds=@seconds-1
SET @DurationEnd= DATEADD(s, -@seconds, @DurationEnd)

SELECT @milliseconds=DATEDIFF(ms, @DurationStart, @DurationEnd)

SELECT @Duration= ISNULL(CAST(NULLIF(@years,0) AS VARCHAR(10)) + ' years,','')
     + ISNULL(' ' + CAST(NULLIF(@months,0) AS VARCHAR(10)) + ' months,','')    
     + ISNULL(' ' + CAST(NULLIF(@days,0) AS VARCHAR(10)) + ' days,','')
     + ISNULL(' ' + CAST(NULLIF(@hours,0) AS VARCHAR(10)) + ' hours,','')
     + ISNULL(' ' + CAST(@minutes AS VARCHAR(10)) + ' minutes and','')
     + ISNULL(' ' + CAST(@seconds AS VARCHAR(10)) 
     -- UNCOMMENT THEFOLLOWING IF YOU WANT MILLISECONDS INCLUDED
     --+ CASE
            --WHEN @milliseconds > 0
                --THEN '.' + CAST(@milliseconds AS VARCHAR(10)) 
            --ELSE ''
       --END 
     + ' seconds','')

SELECT @Duration
END
GO

Then just call using: DECLARE @return_value int, @Duration varchar(100)

EXEC @return_value = [dbo].[GetOperationDuration] @DurationStart, @DurationEnd, @Duration = @Duration OUTPUT

SELECT @Duration as N'@Duration'

Thought I'd share my 2 cents. This fixes the overflow problems but only works with datetime not datetime2. It probably does not work with leap years or when clocks go backwards/forwards. I haven't tested with either.

declare @startTime datetime = getdate()
declare @endTime   datetime
select  [StartDate]      = @startTime,
        [EndDate]        = @endTime,
        [DD:HH:MM:SS.MS] = right( '00' + convert( varchar(20), datediff(hh, 0, @endTime - @startTime ) / 24 ), 2) + ':' + 
                           right( '00' + convert( varchar(20), datediff(hh, 0, @endTime - @startTime ) % 24 ), 2) + ':' + 
                           substring( convert( varchar(20), @endtime - @startTime, 114 ), 
                                      charindex( ':', convert( varchar(20), @endTime - @startTime, 114 ) ) + 1, 
                                      len( convert( varchar(20), @endTime - @startTime, 114 ) ) )