Human readable DATEDIFF function for SQL Server
All the time, when I’m writing reports I need to display a ‘time elapsed’ metric in a short easy to read manor. Usually I do this with a simple convenience function in whatever language I am writing the GUI in. For reports that are purely in SQL, I have created just such a convenience function for SQL server: dateDiffHumanReadable( startDate, endDate, precision ).
This makes it easy to display date spans.
To see how old I currently am, I would call:
DECLARE @birthday DATETIME SET @birthday = '6/2/1978 16:00:00' PRINT dbo.dateDiffHumanReadable(@birthday, GETDATE(), DEFAULT)
This will return my current age:
30y 114d 38m 11s 620ms
This comes in handy when I have a log table with columns like this:
- process
- timeStart
- timeEnd
Just using DATEDIFF I would have to do this:
SELECT process, CASE WHEN timeStart IS NOT NULL AND timeEnd IS NOT NULL THEN DATEDIFF(s, timeStart, timeEnd) ELSE '' END AS [seconds] FROM logTable
With this function, I can get more informational results just doing this:
SELECT process, dbo.dateDiffHumanReadable(timeStart, timeEnd, DEFAULT) FROM logTable
The third parameter is a precision value for when you don’t need right down to the millisecond returned. Calling this:
DECLARE @birthday DATETIME SET @birthday = '6/2/1978 16:00:00' PRINT dbo.dateDiffHumanReadable(@birthday, GETDATE(), 'y')
This will return just the year of my current age:
30y
Full code
SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO -- ============================================= -- Author: T. Curran -- Create date: 3/20/2008 -- Description: Generates a human readable difference between two dates, in the form '1y 5d 3h 2m 6s 10ms' -- ============================================= CREATE FUNCTION [dbo].[dateDiffHumanReadable] ( @dateStart DATETIME, @dateEnd DATETIME, @PRECISION VARCHAR(2) = 'ms' ) RETURNS VARCHAR(200) AS BEGIN DECLARE @diffAsString VARCHAR(200), @dateScratch DATETIME, @years INT, @days INT, @hours INT, @minutes INT, @seconds INT, @milliseconds INT SET @diffAsString = '' IF (@dateStart IS NULL OR @dateEnd IS NULL) BEGIN RETURN @diffAsString END -- @dateScratch is used as a holding place for us to increment the date so we don't alter @dateStart SET @dateScratch = @dateStart -- years SET @years = DATEDIFF(yy, @dateScratch, @dateEnd) SET @dateScratch = DATEADD(yy, @years, @dateScratch) -- days SET @days = DATEDIFF(d, @dateScratch, @dateEnd) SET @dateScratch = DATEADD(d, @days, @dateScratch) -- hours SET @hours = DATEDIFF(hh, @dateScratch, @dateEnd) SET @dateScratch = DATEADD(hh, @hours, @dateScratch) -- minutes SET @minutes = DATEDIFF(mi, @dateScratch, @dateEnd) SET @dateScratch = DATEADD(mi, @minutes, @dateScratch) -- seconds SET @seconds = DATEDIFF(s, @dateScratch, @dateEnd) SET @dateScratch = DATEADD(s, @seconds, @dateScratch) -- milliseconds SET @milliseconds = DATEDIFF(ms, @dateScratch, @dateEnd) SET @dateScratch = DATEADD(ms, @milliseconds, @dateScratch) -- Build the output string based on the precision -- years IF (@PRECISION = 'y' OR @PRECISION = 'd' OR @PRECISION = 'h' OR @PRECISION = 'm' OR @PRECISION = 's' OR @PRECISION = 'ms') BEGIN IF (@years > 0) BEGIN SET @diffAsString = @diffAsString + CAST(@years AS VARCHAR(10)) + 'y ' END END -- days IF (@PRECISION = 'd' OR @PRECISION = 'h' OR @PRECISION = 'm' OR @PRECISION = 's' OR @PRECISION = 'ms') BEGIN IF (@days > 0) BEGIN SET @diffAsString = @diffAsString + CAST(@days AS VARCHAR(10)) + 'd ' END END -- hours IF (@PRECISION = 'h' OR @PRECISION = 'm' OR @PRECISION = 's' OR @PRECISION = 'ms') BEGIN IF (@hours > 0) BEGIN SET @diffAsString = @diffAsString + CAST(@hours AS VARCHAR(10)) + 'h ' END END -- minutes IF (@PRECISION = 'm' OR @PRECISION = 's' OR @PRECISION = 'ms') BEGIN IF (@minutes > 0) BEGIN SET @diffAsString = @diffAsString + CAST(@minutes AS VARCHAR(10)) + 'm ' END END -- seconds IF (@PRECISION = 's' OR @PRECISION = 'ms') BEGIN IF (@seconds > 0) BEGIN SET @diffAsString = @diffAsString + CAST(@seconds AS VARCHAR(10)) + 's ' END END -- milliseconds IF (@PRECISION = 'ms') BEGIN IF (@milliseconds > 0) BEGIN SET @diffAsString = @diffAsString + CAST(@milliseconds AS VARCHAR(10)) + 'ms ' END END -- the above string concat always ends with a space, if the space is there at the end remove it IF (LEN(@diffAsString) > 0) BEGIN IF (RIGHT(@diffAsString, 1) = '') BEGIN SET @diffAsString = LEFT(@diffAsString, LEN(@diffAsString)) END END RETURN @diffAsString END GO









If you had any doubt that
Sun has