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