CODEWITHSUNDEEP
sqlsql-server-2008datetime
Kishan Gajjar
Kishan Gajjar

Reputation: 1138

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

Upvotes: 11

Views: 68040

Answers (11)

BigJoeNH
BigJoeNH

Reputation: 401

Building off of what @ShaneOss did, I added a few more Formatting options to the T-SQL function

CREATE OR ALTER FUNCTION dbo.FormatDateTimeDiffFN (@DurationStart DATETIME2(2), @DurationEnd DATETIME2(2), @FormatStyle TINYINT )
RETURNS NVARCHAR(100) BEGIN
/*  --> Function does not account for Leap Years
    ---------------------------------------------------------------
    @FormatStyle -- Even number values will NOT have Milliseconds
            0 or 1 = 2:6:25:12:18:8.640
            2 or 3 = 2 years, 6 months, 25 days, 12 hours, 18 minutes and 8.640 seconds
            4 or 5 = 2:6:25:12:18:8.640 [YY:MM:DD:hh:mm:ss.ms]
            6 or 7 = 2y 6m 25d 12h 18m 8.640s   */
    
    DECLARE @Duration VARCHAR(100) 
          , @years        INT
          , @months       INT
          , @days         INT
          , @hours        INT
          , @minutes      INT
          , @seconds      INT
          , @milliseconds INT
          , @IncludeMilliseconds TINYINT = (SELECT @FormatStyle % 2)  /* Even = 0; Odd = 1 */

    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)
    
    IF @FormatStyle = 2 OR @FormatStyle = 3
        BEGIN
            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)) 
                  + CASE WHEN @IncludeMilliseconds = 1 AND @milliseconds > 0 THEN '.' + CAST(@milliseconds AS VARCHAR(10)) ELSE '' END
                  + ' seconds', '')
        END
    ELSE IF @FormatStyle = 4 OR @FormatStyle = 5
        BEGIN
            SELECT @Duration
                  = ISNULL(CAST(NULLIF(@years, 0) AS VARCHAR(10)) + ':', '') 
                  + ISNULL(CAST(NULLIF(@months, 0) AS VARCHAR(10)) + ':', '')
                  + ISNULL(CAST(NULLIF(@days, 0) AS VARCHAR(10)) + ':', '') 
                  + ISNULL(CAST(NULLIF(@hours, 0) AS VARCHAR(10)) + ':', '')
                  + ISNULL(CAST(@minutes AS VARCHAR(10)) + ':', '')
                  + ISNULL(CAST(@seconds AS VARCHAR(10)) 
                    + CASE WHEN @IncludeMilliseconds = 1 AND @milliseconds > 0 THEN '.' + CAST(@milliseconds AS VARCHAR(10)) ELSE '' END
                    + '', '')

            SELECT @Duration = TRIM(@Duration) + ' [' +  
                + CASE WHEN @years > 0 THEN 'YY:' ELSE ''  END
                + CASE WHEN @years > 0 OR @months > 0 THEN 'MM:' ELSE ''  END
                + CASE WHEN @years > 0 OR @months > 0 OR @days > 0 THEN 'DD:' ELSE '' END
                + CASE WHEN @years > 0 OR @months > 0 OR @days > 0 OR @hours > 0 THEN 'hh:' ELSE ''  END
                + CASE WHEN @years > 0 OR @months > 0 OR @days > 0 OR @hours > 0 OR @minutes > 0 THEN 'mm:' ELSE '' END 
                + CASE WHEN @years > 0 OR @months > 0 OR @days > 0 OR @hours > 0 OR @minutes > 0 OR @seconds > 0 THEN 'ss' ELSE '' END
                + CASE WHEN @IncludeMilliseconds = 1 AND @milliseconds > 0 THEN '.ms' ELSE '' END
                + ']'
        END
    ELSE IF @FormatStyle = 6 OR @FormatStyle = 7
        BEGIN
            SELECT @Duration
                  = ISNULL(CAST(NULLIF(@years, 0) AS VARCHAR(10)) + 'y ', '') 
                  + ISNULL(CAST(NULLIF(@months, 0) AS VARCHAR(10)) + 'm ', '')
                  + ISNULL(CAST(NULLIF(@days, 0) AS VARCHAR(10)) + 'd ', '') 
                  + ISNULL(CAST(NULLIF(@hours, 0) AS VARCHAR(10)) + 'h ','')
                  + ISNULL(CAST(@minutes AS VARCHAR(10)) + 'm ', '')
                  + ISNULL(CAST(@seconds AS VARCHAR(10)) 
                  + CASE WHEN @IncludeMilliseconds = 1 AND @milliseconds > 0 THEN '.' + CAST(@milliseconds AS VARCHAR(10)) ELSE '' END
                  + 's', '')
        END
    ELSE  --  @FormatStyle = 0 or 1
        BEGIN
            SELECT @Duration
                  = ISNULL(CAST(NULLIF(@years, 0) AS VARCHAR(10)) + ':', '') + ISNULL( CAST(NULLIF(@months, 0) AS VARCHAR(10)) + ':', '')
                  + ISNULL(CAST(NULLIF(@days, 0) AS VARCHAR(10)) + ':', '') + ISNULL(CAST(NULLIF(@hours, 0) AS VARCHAR(10)) + ':', '')
                  + ISNULL(CAST(@minutes AS VARCHAR(10)) + ':', '')
                  + ISNULL(CAST(@seconds AS VARCHAR(10)) 
                    + CASE WHEN @IncludeMilliseconds = 1 AND @milliseconds > 0 THEN '.' + CAST(@milliseconds AS VARCHAR(10)) ELSE '' END
                    + '', '')
        END

    RETURN TRIM(@Duration)
END
GO

Use the function in a SQL SELECT statement

SELECT dbo.FormatDateTimeDiffFN(GETDATE(), GETDATE() + 936.5126,2)

Upvotes: 0

LeoL
LeoL

Reputation: 93

A lot of these answers are so complex for no reason. You can simply do like this:

SELECT CONVERT(varchar, CONVERT(TIME, END_TIME - START_TIME), 8) as 'ELAPSED_TIME (hh:mm:ss)' 
FROM your_table

Upvotes: 2

Steve
Steve

Reputation: 710

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

Upvotes: 0

ShaneOss
ShaneOss

Reputation: 192

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'

Upvotes: 1

Kyle Cowden
Kyle Cowden

Reputation: 21

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.

Upvotes: 2

Mike Gledhill
Mike Gledhill

Reputation: 29161

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:

enter image description here

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:

enter image description here

Simple, hey ?

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

Upvotes: 27

Strider489
Strider489

Reputation: 67

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.sstatic.net/hOmyJ.png

Upvotes: 0

lynx_74
lynx_74

Reputation: 1761

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)

Upvotes: 23

Marvin Zumbado
Marvin Zumbado

Reputation: 1005

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.

Upvotes: 1

Annu
Annu

Reputation: 562

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

Upvotes: 4

RichardTheKiwi
RichardTheKiwi

Reputation: 107716

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

Upvotes: 4

Related Questions