I found a script on the net and altered it for my needs.
I'd like to know what others think of this and if you can envisage any problems with it?
SET ANSI_NULLS ON
SET QUOTED_IDENTIFIER ON
CREATE PROC [dbo].[zTest_SP_DATEDIFF_TIMER] AS
-- CREATED: 18/08/2009 by Gav B.
-- DESCRIPTION: This procedure calculates the difference between 2 dates right down to the millisecond (0.003sec accuracy in SQL2005, untested in 2008)
-- @I_OverFlows = DATEDIFF(ms,GETDATE()-24.49,GETDATE())
-- This represents the maximum of approx 24 Days 11 Hours 50 Mins before causing an "overflow at runtime".
-- RAND function used below just to give a random number for demonstration purposes.
-- Unknown at time of writting WHY the combination of % and / works, but it seems to quite well...
DECLARE @I INT
SET @I = DATEDIFF(ms,GETDATE()-RAND()*24,GETDATE())
convert(varchar(10), (@I/86400000)) + ' Days ' +
convert(varchar(10), ((@I%86400000)/3600000)) + ' Hours '+
convert(varchar(10), (((@I%86400000)%3600000)/60000)) + ' Mins '+
convert(varchar(10), ((((@I%86400000)%3600000)%60000)/1000)) + ' sec ' +
convert(varchar(10), (((@I%86400000)%3600000)%1000)) + ' ms ' AS [DD:HH:MM:SS:MS]
- Gav B.