Today when i was working, i created a grid then bind it with data. On the grid a column called TimeSpend shows seconds between two different dates. But when i look to that column, i cannot understand it how much time at first time well. So I decided to change that column to a formatted date like yyyy-mm-dd hh:mi:ss .

 

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:        ihsany.com
-- Create date:   11/7/2013
-- Description:   Shows readable difference between two datetime
-- =============================================
CREATE FUNCTION fun_ReadableDateDiff
(
    @Date1 datetime,
    @Date2 datetime
)
RETURNS varchar(100)
AS
BEGIN
    -- Declare the return variable here
    DECLARE @Result varchar(100)
    
    DECLARE @yearDiff INT
    DECLARE @monthDiff INT
    DECLARE @dayDiff INT
    DECLARE @hourDiff INT
    DECLARE @minuteDiff INT
    DECLARE @secondDiff INT
    
    DECLARE @DiffDate DATETIME
    
    SET @Result = 'yyyy-mm-dd hh:mi:ss'
    SET @secondDiff = DATEDIFF(ss,@Date1,@Date2)
    SET @DiffDate = DATEADD(ss,@secondDiff,0)
    
    SET @yearDiff = DATEPART(yy,@DiffDate) - 1900
    IF @yearDiff > 0 BEGIN
        SET @Result = REPLACE(@Result,'yyyy',REPLACE(STR(@yearDiff, 4), SPACE(1), '0'))
    END
    ELSE BEGIN
        SET @Result = REPLACE(@Result,'yyyy','0000')
    END
    SET @monthDiff = DATEPART(mm,@DiffDate) - 1
    IF @monthDiff > 0 BEGIN
        SET @Result = REPLACE(@Result,'mm',REPLACE(STR(@monthDiff, 2), SPACE(1), '0'))
    END
    ELSE BEGIN
        SET @Result = REPLACE(@Result,'mm','00')
    END
    SET @dayDiff = DATEPART(dd,@DiffDate) - 1
    IF @dayDiff > 0 BEGIN
        SET @Result = REPLACE(@Result,'dd',REPLACE(STR(@dayDiff, 2), SPACE(1), '0'))
    END
    ELSE BEGIN
        SET @Result = REPLACE(@Result,'dd','00')
    END
    SET @hourDiff = DATEPART(hh,@DiffDate)
    IF @hourDiff > 0 BEGIN
        SET @Result = REPLACE(@Result,'hh',REPLACE(STR(@hourDiff, 2), SPACE(1), '0'))
    END
    ELSE BEGIN
        SET @Result = REPLACE(@Result,'hh','00')
    END
    SET @minuteDiff = DATEPART(mi,@DiffDate)
    IF @minuteDiff > 0 BEGIN
        SET @Result = REPLACE(@Result,'mi',REPLACE(STR(@minuteDiff, 2), SPACE(1), '0'))
    END
    ELSE BEGIN
        SET @Result = REPLACE(@Result,'mi','00')
    END
    SET @secondDiff = DATEPART(ss,@DiffDate)
    IF @secondDiff > 0 BEGIN
        SET @Result = REPLACE(@Result,'ss',REPLACE(STR(@secondDiff, 2), SPACE(1), '0'))
    END
    ELSE BEGIN
        SET @Result = REPLACE(@Result,'ss','00')
    END
    
    RETURN @Result

END
GO

And the results

 


Happy coding :)

If you like this, follow my RSS channel!