Datediff in Hh:Mm:Ss Format

SQL time difference between two dates result in hh:mm:ss

declare @StartDate datetime, @EndDate datetime

select @StartDate = '10/01/2012 08:40:18.000',@EndDate='10/04/2012 09:52:48.000'

select convert(varchar(5),DateDiff(s, @startDate, @EndDate)/3600)+':'+convert(varchar(5),DateDiff(s, @startDate, @EndDate)%3600/60)+':'+convert(varchar(5),(DateDiff(s, @startDate, @EndDate)%60)) as [hh:mm:ss]

This query will helpful to you.

DATEDIFF in HH:MM:SS format

You shouldn't be converting to time - it is meant to store a point in time on a single 24h clock, not a duration or interval (even one that is constrained on its own to < 24 hours, which clearly your data is not). Instead you can take the datediff in the smallest interval required (in your case, seconds), and then perform some math and string manipulation to present it in the output format you need (it might also be preferable to return the seconds to the application or report tool and have it do this work).

DECLARE @d TABLE
(
  id INT IDENTITY(1,1), 
  StartDateTime DATETIME, 
  EndDateTime DATETIME
);

INSERT @d(StartDateTime, EndDateTime) VALUES 
(DATEADD(DAY, -2, GETDATE()), DATEADD(MINUTE, 15, GETDATE())),
(GETDATE()                  , DATEADD(MINUTE, 22, GETDATE())),
(DATEADD(DAY, -1, GETDATE()), DATEADD(MINUTE,  5, GETDATE())),
(DATEADD(DAY, -4, GETDATE()), DATEADD(SECOND, 14, GETDATE()));

;WITH x AS (SELECT id, StartDateTime, EndDateTime, 
  d = DATEDIFF(SECOND, StartDateTime, EndDateTime),
  a = AVG(DATEDIFF(SECOND, StartDateTime, EndDateTime)) OVER()
  FROM @d
)
SELECT id, StartDateTime, EndDateTime,
  [delta_HH:MM:SS] = CONVERT(VARCHAR(5), d/60/60)
  + ':' + RIGHT('0' + CONVERT(VARCHAR(2), d/60%60), 2)
  + ':' + RIGHT('0' + CONVERT(VARCHAR(2), d % 60), 2),
  [avg_HH:MM:SS] = CONVERT(VARCHAR(5), a/60/60)
  + ':' + RIGHT('0' + CONVERT(VARCHAR(2), a/60%60), 2)
  + ':' + RIGHT('0' + CONVERT(VARCHAR(2), a % 60), 2)
FROM x;

Results:

id  StartDateTime        EndDateTime          delta_HH:MM:SS  avg_HH:MM:SS
--  -------------------  -------------------  --------------  ------------
1   2013-01-19 14:24:46  2013-01-21 14:39:46  48:15:00        42:10:33
2   2013-01-21 14:24:46  2013-01-21 14:46:46   0:22:00        42:10:33
3   2013-01-20 14:24:46  2013-01-21 14:29:46  24:05:00        42:10:33
4   2013-01-17 14:24:46  2013-01-21 14:25:00  96:00:14        42:10:33

This isn't precisely what you asked for, as it won't show just MM:SS for deltas < 1 hour. You can adjust that with a simple CASE expression:

;WITH x AS (SELECT id, StartDateTime, EndDateTime, 
  d = DATEDIFF(SECOND, StartDateTime, EndDateTime),
  a = AVG(DATEDIFF(SECOND, StartDateTime, EndDateTime)) OVER()
  FROM @d
)
SELECT id, StartDateTime, EndDateTime,
  [delta_HH:MM:SS] = CASE WHEN d >= 3600 THEN 
    CONVERT(VARCHAR(5), d/60/60) + ':' ELSE '' END
  + RIGHT('0' + CONVERT(VARCHAR(2), d/60%60), 2)
  + ':' + RIGHT('0' + CONVERT(VARCHAR(2), d % 60), 2),
  [avg_HH:MM:SS] = CASE WHEN a >= 3600 THEN 
    CONVERT(VARCHAR(5), a/60/60) + ':' ELSE '' END
  + RIGHT('0' + CONVERT(VARCHAR(2), a/60%60), 2)
  + ':' + RIGHT('0' + CONVERT(VARCHAR(2), a % 60), 2)
FROM x;

This query changes the delta column in the 2nd row in the above result from 0:22:00 to 22:00.

Calculate time difference in HH:MM:SS

You can use this for With DATETIME

DECLARE @START_DATE datetime DECLARE @END_DATE datetime 
--  Set the timeframe variables 
SET @START_DATE = '2011-01-01 16:00:00' SET @END_DATE = '2011-01-01 22:47:22' 
--  Use datediff to show the results in the 
--  HH:MM:SS format 
SELECT CONVERT(VARCHAR(6), datediff(second, @START_DATE, @END_DATE)/3600) + ':' 
+ RIGHT('0' + CONVERT(VARCHAR(2), (datediff(second, @START_DATE, @END_DATE) % 
3600) / 60), 2) + ':' + RIGHT('0' + CONVERT(VARCHAR(2), datediff(second, 
@START_DATE, @END_DATE) % 60), 2)

Result :

HH:MM:SS
6:47:22

OR With TIME

    --  Declare some variables for the requested timeframe 
DECLARE @START_DATE TIME 
DECLARE @END_DATE TIME--  Set the timeframe variables 

SET @START_DATE = '16:00:00' 
SET @END_DATE = '22:47:22'--  Use datediff to show the results in the 

--  HH:MM:SS format 
SELECT CONVERT(VARCHAR(6), Datediff(second, @START_DATE, @END_DATE)/3600) 
       + ':' 
       + RIGHT('0' + CONVERT(VARCHAR(2), (Datediff(second, @START_DATE, 
       @END_DATE) % 
       3600) / 60), 2) 
       + ':' 
       + RIGHT('0' + CONVERT(VARCHAR(2), Datediff(second, @START_DATE, @END_DATE 
       ) % 60) 
       , 2) AS 'HH:MM:SS'

AS Per Edit

; 
WITH cte 
     AS (SELECT EMPID, 
                Min(TIMEIN)                             timein, 
                Max(TIMEOUT)                            timeout, 
                Cast(COALESCE(TIMEIN, TIMEOUT) AS DATE) d 
         FROM   @T 
         GROUP  BY EMPID, 
                   Cast(COALESCE(TIMEIN, TIMEOUT) AS DATE)) 
SELECT EMPID, 
       D AS Day, 
       CONVERT(VARCHAR(6), Datediff(second, TIMEIN, TIMEOUT)/3600) 
       + ':' 
       + RIGHT('0' + CONVERT(VARCHAR(2), (Datediff(second, TIMEIN, TIMEOUT) % 
       3600) / 
       60), 2) 
       + ':' 
       + RIGHT('0' + CONVERT(VARCHAR(2), Datediff(second, TIMEIN, TIMEOUT) % 60) 
       , 2 ) 
         AS [Hours Worked] 
FROM   cte 
ORDER  BY D ASC

Result

empid   Day            Hours Worked
1       2018-01-10     9:00:00
1       2018-01-11     9:33:00

Resolve After Mistake of preiviuos senario

SELECT FirstIN, LastOUT,

CONVERT(VARCHAR(6), Datediff(second, FirstIN, LastOUT)/3600) 
       + ':' 
       + RIGHT('0' + CONVERT(VARCHAR(2), (Datediff(second, FirstIN, LastOUT) % 
       3600) / 
       60), 2) 
       + ':' 
       + RIGHT('0' + CONVERT(VARCHAR(2), Datediff(second, FirstIN, LastOUT) % 60) 
       , 2 ) AS 

       HoursSpent,[Date] FROM (

SELECT
FirstIN    = CAST(MIN([TimeIn]) AS TIME), 
LastOUT    = CAST(MAX([TimeOut]) AS TIME), 
HoursSpent = DATEDIFF(HOUR, CAST(MIN(TimeIn) AS TIME), CAST(MAX(TimeOut) AS TIME)),
CAST(COALESCE(TimeIn, TimeOut) AS DATE) [Date]

FROM @T

GROUP BY
CAST(COALESCE(TimeIn, TimeOut) AS DATE)) AS a

DateDiff for [datetime2](7) in hh:mm;ss format

You can try the following query.

SELECT CONVERT(VARCHAR(12), DATEADD(MS, DATEDIFF(MS,startDate, endDate), 0), 108) AS TimeDiff from Duration

How to subtract two datetime values and get the output in HH:MM:SS format in SQL Server?

Something like:

with q as
(
  select datediff(SECOND, '2019-07-16 04:45:03.000','2019-07-17 19:38:13.000') secs
), hms as
(
  select secs/60/60 as hours,  (secs/60) % 60 as minutes , secs % 60 seconds
  from q
)
select concat(format(hours,'00'),':',format(minutes,'00'),':',format(seconds,'00') ) hms
from hms

which returns

38:53:10

SQL - datediff returns hours, but I want to add minutes and seconds in HH:MM:SS

You can convert the number of seconds as a time. I don't easily follow the logic of your code, but the conversion is simple enough:

select cast(dateadd(second,
                    datediff(second, <starttime>, <enddatime>),
                    0) as time
           )

What is the best way to calculate the difference in format hh:mm:ss from a date to end of date?

You can use calculate the next date by dateadd, and then use datediff to find the seconds to next day, and convert the result to timestamp.

set t = to_timestamp('2019-10-11 16:47:44.042');

select to_varchar( to_timestamp( DATEDIFF('ms', $t,  dateadd( 'day',1,  date_trunc('DAY', $t )) ) / 1000 ), 'HH24:MI:SS.FF3');

Dividing to 1000 is a trick that you can not find anywhere else! :)

You can create a function based on the above formula:

create or replace function timetoendofday ( t timestamp )
returns varchar
as 'select to_varchar( to_timestamp( DATEDIFF(''ms'', t, dateadd( ''day'',1,  date_trunc(''DAY'', t )) ) / 1000 ), ''HH24:MI:SS.FF3'')';

Now it's simple to use:

select timetoendofday( '2019-10-11 16:47:44.042' );
-- returns 07:12:15.958

SQL. Convert datediff hours to dd.hh:mm:ss

watch out for the case where the 'time' in the start day has not been reached in the end date

 select cast(DATEDIFF(SECOND,MIN(OLDESTRECORD),GETDATE()) /  86400  as varchar(4)) + '.' +  CONVERT(CHAR(8),DATEADD(SECOND,DATEDIFF(SECOND,MIN(OLDESTRECORD),GETDATE()) % 86400 ,0),108)