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:

span style="color: #FF0000;">'6/2/1978 16:00:00'

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:

span style="color: #FF0000;">''

With this function, I can get more informational results just doing this:

The third parameter is a precision value for when you don’t need right down to the millisecond returned. Calling this:

span style="color: #FF0000;">'6/2/1978 16:00:00''y')

This will return just the year of my current age:
30y

Full code

span style="color: #008080;">-- =============================================
-- Author:		T. Curran
-- Create date: 3/20/2008
-- Date modified: 3/12/2009
-- Description:	Generates a human readable difference between two dates, in the form '1y 5d 3h 2m 6s 10ms'
-- =============================================
'ms'''-- @dateScratch is used as a holding place for us to increment the date so we don't alter @dateStart
-- years
-- days
-- milliseconds
-- seconds  
-- minutes  
-- Build the output string based on the precision
 
	-- years
'y''d''h''m''s''ms''y '-- days
'd''h''m''s''ms''d '-- hours
'h''m''s''ms''h '-- minutes
'm''s''ms''m '-- seconds
's''ms''s '-- milliseconds
'ms''ms '-- the above string concat always ends with a space, if the space is there at the end remove it
''

UPDATE:
Adam correctly pointed out that my function didn’t work when the time span was less than 1 day because:
SELECT DATEDIFF(dd, ’11:50:48.000′, ’12:12:45.000′)
Will return 1 rather than 0.

I have updated the function to use DATEDIFF to calculate the year, then use a ‘ms’ calculation to count up for the other values.