I had another employee here create a report that lists transactions from the transaction ledger for customers where the previous balance doesn’t match the balance on the next transaction, using SQL Server’s LEAD and LAG functions. To help quickly identify spans of time, next to the time I wanted something like ‘5s’, ’44m 5s’, or ‘2d 0h 45m 5s’. My eyes would automatically go to longer strings and identify those as a larger span of time.
-- User Defined Function
CREATE FUNCTION [dbo].[Humanize_DateDiff]
(
@t1 DATETIME2(7),
@t2 DATETIME2(7),
@showOnlyItems VARCHAR(50) = NULL
)
RETURNS VARCHAR(MAX)
AS BEGIN
/*
PURPOSE
Pretty prints a time difference between 2 dates.
@showOnlyItems should be NULL to include all fields, or a comma delimited list
(with no spaces) of any combination of D,H,M,S,MS.
This won't print any zero-unit items until at least one non-zero unit is printed. For example,
if there are no days, then '0d ' won't be printed at all.
TEST: [
SELECT dbo.Humanize_DateDiff('12/1/2016', '12/25/2016 3:45 AM', NULL); -- 24d 3h 45m 12s 678ms
SELECT dbo.Humanize_DateDiff('12/1/2016', '12/25/2016 3:45:12.678 AM', 'd,h,m'); -- 24d 3h 45m
]
REVISION HISTORY
20170823 DKD
Created
*/
/* for testing.
DECLARE @t1 DATETIME2(7) = DATEADD(MILLISECOND, -((1000 * 60 * 60 * 24 * 3) + 4890976), SYSUTCDATETIME());
DECLARE @t2 DATETIME2(7) = SYSUTCDATETIME();
DECLARE @showOnlyItems VARCHAR(50) = 'D,H,M,S'; -- Comma delimited, no spaces between items, only use combos of ms,s,m,h,d; NULL = show everything
--*/
---
DECLARE @op VARCHAR(MAX) = '';
DECLARE @t_total BIGINT = 0;
DECLARE @t_ms BIGINT, @show_ms BIT = 1;
DECLARE @t_s INT, @show_s BIT = 1;
DECLARE @t_m INT, @show_m BIT = 1;
DECLARE @t_h INT, @show_h BIT = 1;
DECLARE @t_d INT, @show_d BIT = 1;
IF (@showOnlyItems IS NOT NULL) BEGIN
SET @showOnlyItems = ',' + @showOnlyItems + ',';
IF (CHARINDEX(',d,', @showOnlyItems) = 0) SET @show_d = 0;
IF (CHARINDEX(',h,', @showOnlyItems) = 0) SET @show_h = 0;
IF (CHARINDEX(',m,', @showOnlyItems) = 0) SET @show_m = 0;
IF (CHARINDEX(',s,', @showOnlyItems) = 0) SET @show_s = 0;
IF (CHARINDEX(',ms,', @showOnlyItems) = 0) SET @show_ms = 0;
END;
SET @t_total = DATEDIFF(MILLISECOND, @t1, @t2);
-- Days
SELECT @t_d = FLOOR(@t_total / (1000 * 60 * 60 * 24));
SELECT @t_total = @t_total - (@t_d * 1000 * 60 * 60 * 24);
--PRINT '@t_d = ' + COALESCE(CAST(@t_d AS VARCHAR(MAX)), '') + '; @t_total = ' + COALESCE(CAST(@t_total AS VARCHAR(MAX)), '');
IF ((@op != '' OR @t_d > 0) AND @show_d = 1) SELECT @op = @op + ' ' + CAST(@t_d AS VARCHAR(MAX)) + 'd';
-- Hours
SELECT @t_h = FLOOR(@t_total / (1000 * 60 * 60));
SELECT @t_total = @t_total - (@t_h * 1000 * 60 * 60);
--PRINT '@t_h = ' + COALESCE(CAST(@t_h AS VARCHAR(MAX)), '') + '; @t_total = ' + COALESCE(CAST(@t_total AS VARCHAR(MAX)), '');
IF ((@op != '' OR @t_h > 0) AND @show_h = 1) SELECT @op = @op + ' ' + CAST(@t_h AS VARCHAR(MAX)) + 'h';
-- Minutes
SELECT @t_m = FLOOR(@t_total / (1000 * 60));
SELECT @t_total = @t_total - (@t_m * 1000 * 60);
--PRINT '@t_m = ' + COALESCE(CAST(@t_m AS VARCHAR(MAX)), '') + '; @t_total = ' + COALESCE(CAST(@t_total AS VARCHAR(MAX)), '');
IF ((@op != '' OR @t_m > 0) AND @show_m = 1) SELECT @op = @op + ' ' + CAST(@t_m AS VARCHAR(MAX)) + 'm';
-- Seconds
SELECT @t_s = FLOOR(@t_total / 1000);
SELECT @t_total = @t_total - (@t_s * 1000);
--PRINT '@t_s = ' + COALESCE(CAST(@t_s AS VARCHAR(MAX)), '') + '; @t_total = ' + COALESCE(CAST(@t_total AS VARCHAR(MAX)), '');
IF ((@op != '' OR @t_s > 0) AND @show_s = 1) SELECT @op = @op + ' ' + CAST(@t_s AS VARCHAR(MAX)) + 's';
-- Milliseconds
SELECT @t_ms = @t_total;
SELECT @t_total = @t_total - (@t_ms); -- Should == ZERO now
--PRINT '@t_ms = ' + COALESCE(CAST(@t_ms AS VARCHAR(MAX)), '') + '; @t_total = ' + COALESCE(CAST(@t_total AS VARCHAR(MAX)), '');
IF ((@op = '' OR @t_ms > 0) AND @show_ms = 1) SELECT @op = @op + ' ' + CAST(@t_ms AS VARCHAR(MAX)) + 'ms';
SET @op = LTRIM(@op);
--SELECT @op;
RETURN @op;
END
GO
Note that I didn’t have the code recalculate figures if a time format was excluded, meaning that if I were to exclude days, the hours wouldn’t reflect the number of days.
Double D's Blog 