The impetus behind this function is that lately I’ve been asked by couple of fellow developers about a way to find out difference between two datetime values in hh:mm:ss format.
The function below will give you time difference between two datetime values in hh:mm:ss format.Regardless of order in which you give dates it calculates the difference between them and give you result in hh:mm:ss format
IF EXISTS (SELECT 1 FROM INFORMATION_SCHEMA.ROUTINES WHERE ROUTINE_SCHEMA=’dbo’ AND ROUTINE_NAME=’GetTimeDiff’)
DROP FUNCTION GetTimeDiff
GO
CREATE FUNCTION GetTimeDiff
(@Start datetime,
@End datetime)
RETURNS varchar(15)
AS
BEGIN
DECLARE @DateDiff datetime,@TimeDiff varchar(15)
SELECT @DateDiff=@Start,
@Start=CASE WHEN @End<@Start THEN @End ELSE @Start END,
@End = CASE WHEN @End<@DateDiff THEN @DateDiff ELSE @End END
SET @DateDiff= dateadd(ss,datediff(ss,@Start,@End),0)
SELECT @TimeDiff=CAST((DATEDIFF(dd,0,@DateDiff)* 24) + CONVERT(varchar(2),DATEADD(dd,-1 * DATEDIFF(dd,0,@DateDiff),@DateDiff),108)*1 AS varchar(4)) + ‘:’ + RIGHT(CONVERT(varchar(8),DATEADD(dd,-1 * DATEDIFF(dd,0,@DateDiff),@DateDiff),108),5)
RETURN @TimeDiff
END
example usage
SELECT dbo.GetTimeDiff(‘2010-03-26 13:30:15′,’2010-03-01 12:30’)
