Object Oriented Adam

The DateDiff function is frustrating.  You can only get a single element (seconds, minutes, hours, days, months or years).  At work we currently use the DateDiff function for DTS statistics.  Its handy cause you can get an idea of where your spending your time durring DTS execution.  However, it’s not very reasonable.  I wanted a function to return Human Readable text…not just the number of seconds or minutes between dates.

WireLust has a good article here.  However this function uses DATEDIFF and this function has its flaws.

Take a look at this…

SELECT dbo.DateDiffHumanReadable('11:50:48.000', '12:12:45.000', 's')

RESULT
----------------------
1h

(1 row(s) affected)

The code below is my implementation of a HumanReadable DateDiff. There are imperfections with the DateDiff function. My function gets the total seconds between 2 dates and derives the Seconds, Minutes, Hours and Days. Since we use the function for DTS Execution metrics we don’t need anything more than hours…but I included days cause it was easy to do.

My function would return this…

SELECT dbo.DateDiffHumanReadable('11:50:48.000', '12:12:45.000'c)

RESULT
----------------------
21m 57s 

(1 row(s) affected)

So here it is the Improved Human Readable DateDiff…

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		ADAM CLARK
-- Create date: 3/12/2009
-- Description:	Generates a human readable
--				difference between two dates
-- =============================================
CREATE FUNCTION [dbo].[dateDiffHumanReadable]
(
		@dateStart DATETIME,
		@dateEnd DATETIME
)
RETURNS VARCHAR(200)
AS
BEGIN
	DECLARE
		@diffAsString VARCHAR(200),
		@TotalSeconds INT

	DECLARE
		@day INT,
		@hour INT,
		@minute INT,
		@second INT

	SET @second = 0
	SET @minute = 0
	SET @hour = 0
	SET @day = 0

	SET @diffAsString = ''
	IF (@dateStart IS NULL OR @dateEnd IS NULL) BEGIN
		RETURN @diffAsString
	END

	SET @TotalSeconds = DATEDIFF(ss, @dateStart, @dateEnd);
	IF (@TotalSeconds < 0) BEGIN
		RETURN 'ERR: StartDate After EndDate'
	END

	-- seconds
	IF @TotalSeconds < 60 BEGIN
		SET @second = @TotalSeconds
	END
	ELSE BEGIN
		SET @minute = @TotalSeconds / 60
		SET @second = @TotalSeconds - (@minute * 60)

		-- minutes
		IF @minute > 59 BEGIN
			SET @hour = @minute / 60
			SET @minute = @minute - (@hour * 60)

			--hours
			IF @hour > 23 BEGIN
				SET @day = @hour / 24
				SET @hour = @hour - (@day * 24)
			END
		END
	END

	-- Build the output string
	IF @day <> 0 BEGIN
		SET @diffAsString = @diffAsString + CAST(@day AS VARCHAR(10)) + 'd ' + CAST(@hour AS VARCHAR(10)) + 'h ' + CAST(@minute AS VARCHAR(10)) + 'm ' + CAST(@second AS VARCHAR(10)) + 's '
	END
	ELSE BEGIN
		IF @hour <> 0 BEGIN
			SET @diffAsString = @diffAsString + CAST(@hour AS VARCHAR(10)) + 'h ' + CAST(@minute AS VARCHAR(10)) + 'm ' + CAST(@second AS VARCHAR(10)) + 's '
		END
		ELSE BEGIN
			IF @minute <> 0 BEGIN
				SET @diffAsString = @diffAsString + CAST(@minute AS VARCHAR(10)) + 'm ' + CAST(@second AS VARCHAR(10)) + 's '
			END
			ELSE BEGIN
				IF @second <> 0 BEGIN
					SET @diffAsString = @diffAsString + CAST(@second AS VARCHAR(10)) + 's '
				END
				ELSE BEGIN
					SET @diffAsString = '0s'
				END
			END
		END
	END
	RETURN @diffAsString
END
GO