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

Test udf_DurationHourMinSec()

Paste the result in Excel 2013 to verify the result:

Verify udf_DurationHourMinSec() in Excel 2013

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

Keep it simple :-)

Leave a comment

Your email address will not be published. Required fields are marked *