Convert DateDiff into Hours, Minutes, Seconds, Milliseconds

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
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)
--
-- NOTES:
-- @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())

SELECT
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]

SELECT
convert(varchar(10), @I/86400000) + ' Days ' +
convert(varchar(10), (@I%86400000)/3600000) + ' Hours '+
convert(varchar(10), (@I%3600000)/60000) + ' Mins '+
convert(varchar(10), (@I%60000)/1000) + ' sec ' +
convert(varchar(10), @I%1000) + ' ms ' AS [DD:HH:MM:SS:MS]

select
*,
Days = datediff(dd,0,DateDif),
Hours = datepart(hour,DateDif),
Minutes = datepart(minute,DateDif),
Seconds = datepart(second,DateDif),
MS = datepart(ms,DateDif)
from
(
select
DateDif = EndDate-StartDate,
aa.*
from
( -- Test Data
Select
StartDate = convert(datetime,'20090213 02:44:37.923'),
EndDate = convert(datetime,'20090715 13:24:45.837')
) aa
) a

DateDif StartDate EndDate Days Hours Minutes Seconds MS
----------------------- ----------------------- ----------------------- ---- ----- ------- ------- ---
1900-06-02 10:40:07.913 2009-02-13 02:44:37.923 2009-07-15 13:24:45.837 152 10 40 7 913

(1 row(s) affected)

jcrawf02 (8/19/2009)

Gav B (8/18/2009)

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
GO
SET QUOTED_IDENTIFIER ON
GO
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)
--
-- NOTES:
-- @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())

SELECT
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.

I can envisage a problem. You didn't credit your source for the script that you "found on the net". Granted, you altered it, but alteration is not authoring, especially since your alteration may have been a minor detail, we have no way of knowing.