T-SQL: udf_DurationHourMinSec() – Duration between start date and end date

I built a function for reporting purposes that shows the duration in hh:mm:ss between two datetime columns. It can be used in a reporting tool to show the data in a log.

The max difference in DATEDIFF() is 68 years. Quick and extended calculation (every year is a leap year):
68 years * 366 days * 24 hours = 597,312 hours. The returned datatype is VARCHAR(12) (two chars for seconds, two chars for minutes, two chars for delimiters and 6 chars for hours)

Create Database.

USE [master];
GO

IF (DB_ID('PL') IS NOT NULL)
	BEGIN
		ALTER DATABASE PL SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
		DROP DATABASE PL;
	END
GO

CREATE DATABASE PL;
GO

USE PL;
GO

CREATE SCHEMA pl;
GO

Create Function.

-- =============================================
-- Author:		Peter Lalovsky
-- Create date: 2015-01-13
-- Description:	Returns the duration in hh:mm:ss between @TimeStart and @TimeEnd
-- =============================================

CREATE FUNCTION [pl].[udf_DurationHourMinSec]
	(
		@TimeStart DATETIME
		, @TimeEnd DATETIME
	)
	RETURNS VARCHAR(12)
AS

BEGIN
	DECLARE @Duration VARCHAR(12)
	
	SELECT @Duration =
		CASE
			WHEN LEN(CONVERT(VARCHAR, (DATEDIFF(SS, @TimeStart, @TimeEnd)) / 3600)) = 1
				THEN RIGHT('0' + CONVERT(VARCHAR, (DATEDIFF(SS, @TimeStart, @TimeEnd)) / 3600), 2)
			ELSE CONVERT(VARCHAR, (DATEDIFF(SS, @TimeStart, @TimeEnd)) / 3600)
		END
		+ ':' + RIGHT('0' + CONVERT(VARCHAR, (DATEDIFF(SS, @TimeStart, @TimeEnd)) % 3600 / 60), 2)
		+ ':' + RIGHT('0' + CONVERT(VARCHAR, (DATEDIFF(SS, @TimeStart, @TimeEnd)) % 60), 2)
	
	RETURN @Duration
END
GO

Test Function.

DECLARE @TestTable TABLE
	(
		[Order] INT
		, DateTimeStart DATETIME
		, DateTimeEnd DATETIME
	);

INSERT @TestTable ([Order], DateTimeStart, DateTimeEnd)
SELECT 1, '1974-07-30 17:48:56.197', '1974-08-02 12:32:18.133'
UNION SELECT 2, '1987-01-13 14:07:07.477', '1987-01-12 14:07:19.243'
UNION SELECT 3, '1915-06-17 14:07:07.477', '1915-06-17 14:07:19.243'
UNION SELECT 4, '1900-01-01 12:24:53.020', '1967-12-17 12:14:30.077';

SELECT 
	*
	, PL.pl.udf_DurationHourMinSec(DateTimeStart, DateTimeEnd) AS [Duration]
FROM @TestTable;
GO

Paste the result in Excel 2013 to verify the result:

595727 – 595703 = 24 hours
Just for fun I will say that there is one mischievous day in a leap year ;-)

Delete Database.

USE [master];
GO

IF (DB_ID('PL') IS NOT NULL)
	BEGIN
		ALTER DATABASE PL SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
		DROP DATABASE PL;
	END
GO

2022-03-02: Add days in format d.HH:mm:ss as explained here

2022-03-02: Add days in format d.HH:mm:ss as explained here

DROP TABLE IF EXISTS #Events;

CREATE TABLE #Events
(
  date_start datetime NOT NULL
  , date_end datetime NOT NULL
);

INSERT  #Events (date_start, date_end)
SELECT           '2007-01-01 06:34:12', '2007-01-01 12:45:34'
UNION ALL SELECT '2007-01-02 09:23:08', '2007-01-02 17:05:37'
UNION ALL SELECT '2007-01-03 16:34:12', '2007-01-17 16:55:18'
UNION ALL SELECT '2007-01-04 11:02:00', '2007-01-04 14:53:21'
UNION ALL SELECT '2007-01-05 07:52:55', '2007-01-05 09:08:48'
UNION ALL SELECT '2007-01-06 19:59:11', '2007-01-07 01:23:11'
UNION ALL SELECT '2007-01-07 03:12:23', '2007-01-09 20:02:25';

SELECT * FROM #Events;

------------------------------

SELECT 
  CONCAT(
    (S / 86400)
    , '.', RIGHT(CONCAT('00', ((S % 86400) / 3600)), 2)
    , ':', RIGHT(CONCAT('00', ((S % 3600) / 60)), 2)
    , ':', RIGHT(CONCAT('00', (S % 60)), 2)
  ) AS [Duration d.HH:mm:ss]
FROM
  (
  	SELECT DATEDIFF(SS, date_start, date_end) AS S 
  	FROM #Events
  ) AS X

Keep it simple :-)